Jump Start: Using Stored Procedure Variables to Build Dynamic SQL

In my past few Jump Start columns, I've covered various aspects of creating SQL Server Express stored procedures. Most recently, in "Stored Procedure Variables" (www.sqlmag.com/Article/ArticleID/97510 ), I showed you the basics of declaring variables, assigning values to them, and displaying their contents. This week, I want to take a deeper look at using T-SQL variables in stored procedures. Before jumping into a code example, however, I should point out that although I've been discussing variables in the context of stored procedures, variables can also be used in any T-SQL script or batch.

If declaring a variable and assigning a value to it were all that you could do with variables, they'd be no better than constants. The real value of a variable is that its value can change during the execution of the stored procedure or script.

One especially common use for variables is in constructing dynamic SQL statements. A dynamic SQL statement is essentially one that's built on the fly, typically by combining user-supplied values with predetermined SQL keywords. However, bear mind that dynamic SQL is open to SQL Injection attacks and is not the best technique to use for Web applications. (For information about SQL Injection attacks, see "Preventing SQL Injection Attack," www.sqlmag.com/Articles/ArticleID/43012/43012.html .)

The following code illustrates how to build a sample dynamic SQL statement:

DECLARE @rowcount INTDECLARE @customerID VARCHAR(10)DECLARE @dynamicSQL NVARCHAR(100)SET @customerid = 'ALFKI'SET @dynamicSQL = 'SELECT @rowcount=count(*) from Orders' +           ' where CustomerID = @customerID'EXEC sp_executesql @dynamicSQL,                   N'@rowcount int out, @customerID varchar(10)',                   @rowcount out,                   @customerIDPRINT 'There are ' + cast(@rowcount as char(3))+ 'orders for ' + @customerID

In this code I declare three variables, then use the SET statement to assign values to them. There's nothing here that we haven't covered before. But the thing to note is that the value assigned to the @dyanmicSQL variable is itself an SQL statement in which the value for CustomerID is assigned at runtime from the variable @customerid. When this code is executed, the SQL statement coded in the variable is created on the fly, then executed using the sp_executesql stored procedure.

As you can see, dynamic SQL can be a powerful technique for making your applications more flexible.