generalized/generic stored procedures

[mamatha_raghu]

hi all, Iam in search of generating generic stored procedures(one procedure for a single operation or multiple for all the tables by passing dynamic queries)is it possible???? and want to know whether parameterized stored procedures are pre-compiled or not????? plz send ur views asap thanks in advance regards mamatha

[Mike Dimmick]

It's a really, really bad idea. SQL Server will generate a query plan for the first set of parameters, cache it, and reuse it for all future calls to that stored procedure regardless of how the parameters change. Stored procedures work best when the shape of the query - the tables involved, how they're joined, the columns filtered by the WHERE clause, and the order of the output - remains stable. They don't work if you try to change the shape of the query. SQL Server accepts parameterized SQL text - with @variables in the command text - and it caches the query plans for parameterized queries. It also tries to automatically deduce where parameters should be if no parameters were used - this is referred to as auto-parameterization. It still caches the query plan even if it can't deduce the parameters. However, it ranks the cached query plans so that a plan for a stored procedure is likely to be kept longer than a parameterized query, which is likely to be kept longer than an auto-parameterized query, which in turn is likely to be kept longer than a non-parameterized query. Unless you really want to prevent users from SELECTing from the base tables - using the security barrier you can erect using stored procedures - I strongly recommend using parameterized, dynamically-constructed queries submitted as text.

DoEvents: Generating unexpected recursion since 1991