Dynamic Where in Stored Procedure help

[jl39775]

Hi all,

I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?

Thanks,

James

 

[SQLSister]

Any time you can avoid dynamic SQL it is better to do so. Dynamic SQL cannot have a precomplied execution plan, so is less eficent. Also, using dynamic SQL you must set permissions at the table level instead of just granting exec on the stored procedure. This is less secure and muh more difficult for the admin to maintain properly. Further, it is harder to properly test such a procedure as the input dictates the structure of teh SQl statement created.

Questions about posting. See faq183-874