Dynamic WHERE clause alternative.

[lllyxer]

I have 3 tables: Authors, Employee and Stores.
I need to create a stored procedure which will take 3 comma delimited parameters, to be able to query above 3 tables.
Basically my front end user can say give me Authors with last name starting from ‘A,B’ and Employee with first name starting from ‘J,N,K’ and Stores with city starting from ‘New, Los’.
So, stored procedure call will look like this Exec myStoredProcedure 'A,B' , 'J,N,K', 'New,Los' .

My question is, how should I handle WHERE clause in stored procedure if I don’t want it to be a “dynamic WHERE”.

Thank you.

 

[rac2]

I think you must parse those parameters to begin with. So the question becomes which is more efficient, a block of code for parsing or dynamic sql.

Then, after the parsing what to do with the pieces? INSERT them in a temporary table so you can write a query like

SELECT *
FROM MyTable 
WHERE col_a IN (
   SELECT col_a_criteria
  FROM #temp_table
)

I cant imagine that whole approach would be faster than a dynamic query.

But I am really curious to see what the others have to say.