In the six years I've worked with SQL Server, there's been an ongoing debate about whether stored procedures or dynamically-generated SQL strings are the better choice for certain jobs.
Stored procedures are useful when you have a function that changes relatively little or has parameters easily encompassed within the syntax of a stored procedure. Dynamic SQL on the other hand, is generally for quick-and-dirty work. It's also used when dealing with something that needs to be assembled almost entirely from scratch; For instance when the number and variety of parameters, the tables being queried, and so on may vary from statement to statement. But even then a stored procedure is suitable and in the long run the better choice.
The Rule…
Most SQL Server programmers try to consolidate their code into stored procedures for several reasons.
Performance. This is one of those "everyone knows this" facts, but it bears repeating. Stored procedures are cached and reused a lot more efficiently than dynamic SQL. Although there is some ongoing debate about the speed of stored procedures, there's not much question that in a large-scale multi-user application they show the best benefits.
Consistency. If you're a programmer, it's easier to reference a stored procedure to do something that may be invoked in multiple contexts than it is to rewrite the same SQL strings in different places (and possibly make a mistake doing so). It also makes it easier to incrementally upgrade code throughout a large application; the references to the underlying stored procedure can be changed much more gracefully. Trying to replace all the instances of a dynamically-generated SQL block throughout an application is a horror no one should ever have to deal with.
Security. Stored procedures provide a way to do granular security on certain functions, especially if your database is used by more than one application at once. Dynamically-generated SQL statements are not really "owned" by anyone — the only permissions in force are the user contexts used to run them in — so it's harder to enforce security.
….And The Exception
There are several reasons why some people choose dynamic SQL over stored procedures. On closer inspection, though, the their decision stems from a lack of acquaintance for how many existing T-SQL functions can provide the same effects.
Quick-and-dirty deployment. This is probably the most commonplace reason for using dynamic SQL. In the context of a given application, it's just easier and faster to throw together a SQL string and pass it to SQL Server rather than develop a stored procedure on the backend to do the same thing.
What's problematic is that if this "quick-and-dirty" approach turns into an application-wide habit — or mushrooms into a big one. What was just a convenient way to get things done now turns into a long-term maintenance nightmare. In the long run, any application that passes more than a few queries should have them replaced with manageable stored procedures.
Doing work on multiple databases or tables. Another common use (abuse?) of dynamic SQL I often see is constructing a series of T-SQL statements that each run against a different table or database. Another way to do this with multiple databases is to use a Microsoft stored procedure, sp_MSforeachdb, which accepts a T-SQL statement as a parameter and runs said statement on every database in the instance of SQL Server you're connected to. This is not a perfect solution since the procedure itself is officially undocumented and could cease to exist or stop working at some point.
Passing columns or tables as parameters. Some SQL statements simply cannot be rendered elegantly as a stored procedure. One scenario is a piece of SQL that has a column or table name passed to it as a parameter (or as part of a parameter), which isn't allowed in the context of a stored procedure.
A possible way to get around this is to create a stored procedure that works as a wrapper for the EXEC sp_executesql statement. In essence, use a stored procedure to generate dynamic SQL. This could even be used to dynamically create a stored procedure that works only on a specific table or column as needed, so it could be re-used in the future. One danger, however, is if you don't have the data sanitized beforehand, it can become a possible vector for SQL injection attacks.
Many of the circumstances involving dynamically-passed parameters can be solved in the context of a stored procedure. A similar situation involves a dynamic sort condition, where a passed parameter would be used in an ORDER BY statement. If the number of possible parameters is relatively small, you could simply use an ORDER BY CASE statement with the variable to deal with this in a clean way. If you wanted to pass a parameter to select the TOP n rows of a query, you could use SET ROWCOUNT in the context of a stored procedure to achieve the same exact effect.
I should point out that if you constantly find yourself in a position where you're forced to pass table names or column names as parameters, the real culprit is probably not SQL Server's limitations but a data design that doesn't encompass your real needs. This is not always the case, but it happens often enough, where you may want to take a hard look at how your schema is put together to make sure you're not fighting the wrong battle. Do not try to fight against the odds of a badly-designed database and make it do work it was never intended to do.
About the author:Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information on SearchSQLServer.com
Expert Advice: FAQ: SQL Server stored procedure how-tos
Tip: Upgrading stored procedures in SQL Server 2005
Guide: Don't expose interfaces that create dynamic SQL to the end user
