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.

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

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

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

 SELECT @TotalRowsReturned