Content area
Full text
When done correctly, it can be used effectively
One comment that I hear repeatedly but that's totally without merit is "The use of dynamic SQL is bad." So what does "bad" really mean in this case? It can mean many things, depending on who made the comment. When used incorrectly, dynamic SQL certainly has some downsides. But when used properly, it has many positive attributes, including the ability to enhance performance in several ways, as you'll see shortly. Let's sec if I can make a believer out of you.
What Does It All Mean?
To begin, let's define two terms: dynamic SQL and ad-hoc statement. Dynamic SQL occurs when one or more T-SQL statements are dynamically pieced together inside a stored procedure or T-SQL code block and executed with cither the EXECUTE command (which can be shortened to EXEQ or the sp_executesql system stored procedure. An adhoc statement is a T-SQL statement that's built by a client application and executed directly as a batch call to SQL Server. However, for the most part, you can equate the ad-hoc building of T-SQL statements to how the EXEC command is used.
I'll explain the two methods for executing dynamic SQL in a little more detail, but keep in mind that both EXEC and sp_executesql are fully documented in SQL Server Books Online (BOL). Both methods are commonly used to dynamically build a statement based on a set of parameters passed into a stored procedure or a set of values obtained by querying metadata. To simplify things, I'll use a series of variables to simulate a set of values.
First, let's talk about the EXEC command. It...