Generate dynamic SQL statements in SQL Server

When you need to solve a tricky database problem, the ability to generate SQL statements is a powerful tool--although you must be careful when using it. This article explores how you can use this functionality to generate SQL statements on the fly.

Dynamic SQL statements
A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.

It can be useful to construct these statements dynamically when you need to decide at run time what fields to bring back from SELECT statements; the different criteria for your queries; and perhaps different tables to query based on different conditions.

These SQL strings are not parsed for errors because they are generated at execution time, and they may introduce security vulnerabilities into your database. Also, SQL strings can be a nightmare to debug, which is why I have never been a big fan of dynamically built SQL statements; however, sometimes they are perfect for certain scenarios.

A dynamic example
The question I answer most often is, "How can I pass my WHERE statement into a stored procedure?" I usually see scenarios similar to the following, which is not valid TSQL syntax:

DECLARE @WhereClause NVARCHAR(2000)
 SET @WhereClause = ' Prouct = ''Computer'''     

 SELECT * FROM SalesHistory WHERE @WhereClause

In a perfect world, it would make much more sense to do the following:

DECLARE @Product VARCHAR(20)
 SET @Product = 'Computer'     

 SELECT * FROM SalesHistory WHERE Product = @Product

It isn't always this easy. In some scenarios, additional criteria is needed, and as tables grow wider, more and more criteria is often needed. This can typically be solved by writing different stored procedures for the different criteria, but sometimes the criteria is so different for each execution that covering all of the possibilities in a stored procedure is burdensome. While these stored procedures can be made to take into account every WHERE statement possible depending on different parameters, this often leads to a degradation in query performance because of so many conditions in the WHERE clause.

Let's take a look at how to build a simple dynamic query. First, I need a table and some data to query. The script below creates my SalesHistory table and loads data into it:

CREATE TABLE [dbo].[SalesHistory]
  (        
        [SaleID] [int] IDENTITY(1,1),        
        [Product] [varchar](10) NULL,              
        [SaleDate] [datetime] NULL,               
        [SalePrice] [money] NULL
  )
  GO              

  SET NOCOUNT ON             

  DECLARE @i INT
  SET @i = 1          
  WHILE (@i <=5000)
  BEGIN                                       

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)                    
         VALUES ('Computer', DATEADD(ww, @i, '3/11/1919'),
         DATEPART(ms, GETDATE()) + (@i + 57))                              

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)        
         VALUES('BigScreen', DATEADD(ww, @i, '3/11/1927'),
         DATEPART(ms, GETDATE()) + (@i + 13))                                   

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)            
         VALUES('PoolTable', DATEADD(ww, @i, '3/11/1908'),
         DATEPART(ms, GETDATE()) + (@i + 29))                                          

       SET @i = @i + 1             

  END

Now I will build my stored procedure that accepts a WHERE clause. For the purpose of this example, I will assume that the WHERE clause was built dynamically from the calling client application.

CREATE PROCEDURE usp_GetSalesHistory
 (
         @WhereClause NVARCHAR(2000) = NULL
 )
 AS
 BEGIN
         DECLARE @SelectStatement NVARCHAR(2000)
         DECLARE @FullStatement NVARCHAR(4000)     

         SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '
         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')     

         PRINT @FullStatement
         EXECUTE sp_executesql @FullStatement     

                /*
  --can also execute the same statement using EXECUTE()
         EXECUTE (@FullStatement)      
         */
 END

I set the @WhereClause parameter to allow NULL values because we may not always want to pass a value in for the @WhereClause.

For every execution of this stored procedure, every field is returned for the TOP 5 rows from SalesHistory. If there is a value passed in for the @WhereClause parameter, the executing statement will append that string to the @SelectStatement string. Then I use the stored procedure sp_executesql to execute the dynamically built SQL string.

sp_executesql or EXECUTE()
There are two ways to execute dynamic SQL in SQL Server: use the sp_executesql system stored procedure or the EXECUTE() operator. Sometimes the two methods can produce the same result, but there are differences in how they behave.

The system stored procedure sp_executesql allows for parameters to be passed into and out of the dynamic SQL statement, whereas EXECUTE() does not. Because the SQL statement is passed into the sp_executesql stored procedure as a parameter, it is less suseptible to SQL injection attacks than EXECUTE(). Since sp_executesql is a stored procedure, passing SQL strings to it results in a higher chance that the SQL string will remain cached, which should lead to better performance when the same SQL statement is executed again. In my opinion, sp_executesql results in code that is a lot cleaner and easier to read and maintain. These reasons are why sp_executesql is the preferred way to execute dynamic SQL statements.

In my previous example, I looked at how you can build a simple SQL statement by passing a WHERE clause into a stored procedure. But what if I want to get a list of parameter values from my dynamically built SQL statement? I would have to use sp_executesql because it is the only one of my two options that allows for input and output parameters.

I am going to slightly modify my original stored procedure so that it will assign the total number of records returned from the SQL statement to an output parameter.

DROP PROCEDURE usp_GetSalesHistory
 GO
 CREATE PROCEDURE usp_GetSalesHistory
 (
         @WhereClause NVARCHAR(2000) = NULL,          
         @TotalRowsReturned INT OUTPUT
 )
 AS
 BEGIN
         DECLARE @SelectStatement NVARCHAR(2000)
         DECLARE @FullStatement NVARCHAR(4000)
         DECLARE @ParameterList NVARCHAR(500)     

         SET @ParameterList = '@TotalRowsReturned INT OUTPUT'     

 SET @SelectStatement = 'SELECT @TotalRowsReturned 
 = COUNT(*) FROM SalesHistory '
         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')     

         PRINT @FullStatement
             EXECUTE sp_executesql @FullStatement, @ParameterList, 
             @TotalRowsReturned = @TotalRowsReturned OUTPUT
 END
 GO

In the above procedure, I need to declare a parameter list to pass into the sp_executesql stored procedure because a value is being assigned to the variable at run time. The only other change to the sp_executesql call is that I am assigning the output parameter from the call to the local @TotalRowsReturned parameter in my usp_GetSalesHistory stored procedure.

I can even call my usp_GetSalesHistory stored procedure similar to the way I did before, but with the addition of an output parameter to indicate the rows that were returned.

DECLARE @WhereClause NVARCHAR(2000), @TotalRowsReturned INT
 SET @WhereClause = 'WHERE Product = ''Computer'''     

 EXECUTE usp_GetSalesHistory
 @WhereClause = @WhereClause,
 @TotalRowsReturned = @TotalRowsReturned OUTPUT     

 SELECT @TotalRowsReturned

Caution
Although I am not a huge fan of using dynamic SQL statements, I believe it is a great option to have in your tool belt.

If you decide to incorporate dynamic SQL into your production level code, be careful. The code is not parsed until it is executed, and it can potentially introduce security vulnerabilities that you do not want.

If you are careful with your dynamic SQL statement, it can help you create solutions to some pretty tricky problems.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.