Data Management

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. Tim Chapman explores how you can use this functionality to generate SQL statements on the fly.

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.

-----------------------------------------------------------------------------------------

Get database tips in your inbox

TechRepublic's free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

8 comments
Tony Hopkinson
Tony Hopkinson

reason to pu it in product level code and building a where clause would not be it. It comes into it's own for admin scripts though. Like rolling through a query from sysdatabases and running dbcc on them and such.

TJ111
TJ111

I wrote a custom SQL Interface class to allow for dynamically created queries on Ajax applications (PHP). It's excellent for situations such as this, and doesn't rely on SQL Procedures to do the work. I verify data in the current script, then send it to overloaded functions which also (by default at least) clean strings and arrays to prevent nasty SQL injections. I think it provides alot more feature-rich query creation (via method overloading and SQL statement creation) and removes alot of security risks involved with dynamic SQL in SQL Procedures. What do other people think of this?

chapman.tim
chapman.tim

I agree...it was more of an "how to do this" than a "you should do this". Also, for your example, I imagine you could use sp_msforeachdb for something like that... Tim

steitelbaum
steitelbaum

it seems to me that the bread and butter of products like RS and SSIS is metadata. Dynamic SQL seems to hide a lot of the things a metadata based product might need at design time. I am a big fan of dynamic sql because every so often a really good reason for using it comes up. Therefore, I'd like to recommend that readers of this article consider how using dynamic sql might eventually force their hand into the use of temp tables, table variables or a trick I just learned of (havent tried it) where a select where 1=0 approach is used to pretend that the query results will be coming from a temp table or table var, when in fact they're really going to come from dynamic sql.

skrahimi
skrahimi

That sounds interesting. I like the approach of not using T/SQL better. Can you post your script?

Tony Hopkinson
Tony Hopkinson

I don't need my trusty cursor on sysdatabases anymore? I ought to keep up to date really. Hmmm you find away of doing things, it works damn fine, some clever arse goes and spoils it and makes you look bad. I shall have to look that up now, or perhaps more grist for your mill?

chapman.tim
chapman.tim

It sounds neat to me. By the way, whether you write it in VB, C++, C#, or whatever, SQL is SQL. I guess you mean you like the idea of not writing it in stored procedures better?