Generate dynamic SQL statements in SQL Server

By Tim Chapman , TechRepublic on April 17, 2008

Summary

Learn to generate on the fly SQL statements, which can be used to solve tricky database problems.

Events

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore

Submit

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.

Talkback

sir, i want to ask that i am working in asp.net with database SQL Server 2005 i am having problem that
i am having 4 table (title,auther,publisher, other description) and having 4 text box in asp form what will be dynamic query and how it will it process if there a user can input a singal or multi option i.e he can fill single textbox or may multi.

Pankaj Sharma August 4, 2010

dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

you can do like this

objConn.execute(sql)

Nidhi March 17, 2011

Add your opinion

In order to post a comment, you need to be registered. (Sign In or register below)

E-mail* (Will not be displayed)

Password*

Username*

Retype your password*

Comment

Post your comment

ZDNet Asia Live

Kodak loses patent ruling against Apple, RIM. http://t.co/N1j7aZ6o

6 minutes ago by zdnetasia on twitter

#radio Radio Serbia by EnjoyIT 1.0 http://t.co/nGQFvX2E

21 minutes ago by RealTonyRocha on twitter

Rise in <b>Chinese</b>-funded acquisitions could trigger more hurdles http://t.co/0pXBS1HR

51 minutes ago by GeorgeHAllenGA on twitter

Rise in Chinese-funded acquisitions could trigger more hurdles: By Ellyne Phneah , ZDNet Asia on May 22, 2012 (6... http://t.co/W3SOdw2c

51 minutes ago by MergeAcquire on twitter

RT @zdnetasia: CFOs increasingly involved in IT investment decisions. http://t.co/8QrfwOSb

1 hour ago by 666hellscream on twitter

CFOs increasingly involved in IT investment decisions http://t.co/XD1LerFq via @zdnetasia #PrivateCloud #SC2012 #CAPEX

1 hour ago by HarishAitharaju on twitter

Rise in Chinese-funded acquisitions could trigger more hurdles. http://t.co/VC3G3m3o

1 hour ago by zdnetasia on twitter

RT @zdnetasia: Rise in Chinese-funded acquisitions could trigger more hurdles. http://t.co/VC3G3m3o

1 hour ago by wrikent3500 on twitter

So much as we know , MTK6575 extremely integrated frequency1GHz ARM Cortex-A9 processor, the superiority of 3G / HSPA Modem, and help the...

1 hour ago by y15822137359 on 5 SaaS adoption speed bumps to avoid

Rise in Chinese-funded acquisitions could trigger more hurdles - ZDNet Asia: Rise in Chinese-funded acquisitions... http://t.co/bZaAQnRL

1 hour ago by MandAWorldwide on twitter

Rise in Chinese-funded acquisitions could trigger more hurdles http://t.co/mIsuZjnU http://t.co/erFX4aVv #arcavir

1 hour ago by V_RaV on twitter

http://t.co/VNaZtseV Rise in Chinese-funded acquisitions could trigger more hurdles: "Cash r... http://t.co/N0gZZEdR http://t.co/wiqY9ktt

1 hour ago by RavtachSolution on twitter

Rise in Chinese-funded acquisitions could trigger more regulatory clearance issues overseas http://t.co/cvLSpTwo #in

1 hour ago by EllyZDNetAsia on twitter

Alibaba seeks $2.3B from shareholders for Yahoo deal. http://t.co/qLRAhRQk

1 hour ago by zdnetasia on twitter

CFOs increasingly involved in IT investment decisions. http://t.co/8QrfwOSb

1 hour ago by zdnetasia on twitter

Quickflix WatchNow 2.0 http://t.co/XWti5VWT

1 hour ago by blog4ipad on twitter

I reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...

1 day ago by wykoong on Gartner: Mobile CRM gives better ROI than social

This video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...

1 day ago by TradeBrother on A quick fill handle trick for Microsoft Excel

waiting...

3 days ago by eapete on What should count in a company's market value?

Boy, you've opened a can of worms now.

Wait for the rants & raves.

3 days ago by eapete on What should count in a company's market value?

I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...

4 days ago by wykoong on Drop the egos, copy ideas, then innovate

ZDNet / Techguide / Database

Generate dynamic SQL statements in SQL Server

Summary

Events

Echelon 2012 June 11 and 12, 2012University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012 June 7 and 8, 201212th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore April 23-25, 2012Grand Hyatt Singapore

Email

Talkback

Resource Centre Useful content from our premier sponsors

Database TechGuides

ZDNet Asia Live

Keep up with ZDNet Asia

Whitepapers

Downloads

Database News

Inside ZDNet Asia

Sponsored

Services

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore