stoke
Honorary Master
KK.1 - By constructing the queries in the Stored Procedure and executing them via sp_ExecuteSql, you execute a structured query (SQL, hey) without @variables in, with explicit values assigned to the clauses and or ordering. I believe in a philosophy of managing as much as possible on the database, thereby reducing the areas re maintenance. It's much quicker to fix a single stored procedure in the database, than it is to fix a query compilation error in your BL layer, compile the whole thing, obfuscate the code and deploying it on the necessary servers.
2 - Fair enough, but you win nothing over Stored Procedures then (except perhaps a maintenance burden).
Good points.
Thanks.