Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

When to Use Dynamic SQL

By Robert Marda,

Places For Dynamic SQL

Introduction

 

For the purposes of this article let me define dynamic SQL as any code you build and store in a variable until you are ready to execute the code.  In my examples I will be using the command EXEC.

 

Most DBA’s and developers prefer not to use dynamic SQL for a number of reasons.  Among the most common reasons to not use dynamic SQL is that the execution plan won’t get cached and that security must be set up differently.  So, with this article I may brand myself as a renegade or someone who doesn’t know what he’s doing.  In any case, I use dynamic SQL whenever it seems to be the best way to get the results I need and I firmly believe there is a place for dynamic SQL in almost every database.

 

Tables For Examples

 

Here is the code that will create the tables and fill them with the data that will be used in the examples that follow:

 

CREATE TABLE #ColumnNames (ColumnID int IDENTITY (1, 1) NOT NULL, ColumnName varchar (50) NULL)

 

CREATE TABLE #ColumnValues (ColumnID int NOT NULL, Value varchar (20) NULL, ClientName varchar (50) NULL)

 

INSERT INTO #ColumnNames (ColumnName)

SELECT 'Has Special License' UNION

SELECT 'Dealer'

 

INSERT INTO #ColumnValues (ColumnID, Value, CLientName)

SELECT 1,'Yes','Database Hobby Shoppe' UNION

SELECT 2,'No','Imaginary Databases Inc' UNION

SELECT 2,'Yes','Super Databases Management'

 

Example 1

 

This first example shows how to get a column name from an existing table and use it as the alias for the column that contains its corresponding data.  This is one way you can allow users to create their own columns and populate them with values by simple inserts into two tables.  Normally, the tables will not be temp tables as used in this example.

 

To my knowledge if you ever need or decide to allow users to do this, the only way to return them a result set using their column name is by using dynamic SQL.  Here is one way you can accomplish what I explained above:

 

DECLARE @Query varchar(300), @ColumnName varchar(50)

 

SELECT @ColumnName = ColumnName

FROM #ColumnNames

WHERE ColumnID = 2

 

SET @Query = '

SELECT ClientName, Value AS [' + @ColumnName + ']

FROM #ColumnValues v

INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID

WHERE n.ColumnID = 2'

 

PRINT @Query

PRINT ''

 

EXEC (@Query)

 

This above example is useful when you want to deliver a result set to someone using the column name defined by a user such as for an excel spreadsheet.

 

Example 2

 

The above technique for column names can also be applied to table names.  If you need a query or stored procedure to get data from a table name specified by a variable then code like this will work:

 

DECLARE @Query varchar(300), @TableName varchar(30)

 

SET @TableName = '#ColumnValues'

 

SET @Query = '

SELECT *

FROM ' + @TableName

 

PRINT @Query PRINT ''

 

EXEC (@Query)

 

The PRINT lines show you what the query looks like.

 

Example 3

 

Often you have a comma delimited list in a variable that you want to use after the IN key word to limit a result set.  I have seen many people, including myself, develop a query similar to this:

 

DECLARE @InList varchar(30)

 

SET @InList = '1,2'

 

SELECT *

FROM #ColumnValues

WHERE ColumnID IN (@InList)

 

If you use just one number in the @InList variable the above query will work.  Of course then you could just use the equals sign instead of the IN key word.  SQL Server tries to convert the list to an int which only works when one number is there.

 

There are many ways to fix this.  Below I show how you can use dynamic SQL to make the query work:

 

DECLARE @InList varchar(30), @Query varchar(300)

 

SET @InList = '1,2'

 

SET @Query = '

SELECT *

FROM #ColumnValues

WHERE ColumnID IN (' + @InList + ')'

 

PRINT @Query PRINT ''

 

EXEC (@Query)

 

The PRINT lines are only used to show you how the query looks with the variable @InList.

 

Example 4

 

When the comma delimited list is not a list of numbers a little extra manipulation must be done to make the query work.  Here is how I have seen this done without dynamic SQL:

 

DECLARE @InList varchar(30)

 

SET @InList = 'yes,no'

 

SELECT *

FROM #ColumnValues

WHERE Value IN (@InList)

 

If you SET @InList equal to ‘yes’ or ‘no’ you will get a result set.  But when using the comma SQL Server tries to find something that matches the entire string of characters.  There are many ways to fix this and all but one use dynamic SQL.  In this article I’ll show you how to fix this using dynamic SQL.  It’s up to you to test this method versus other solutions to see what works best for your database.  Here is how you can make the above code work:

 

 DECLARE @InList varchar(30), @Query varchar(300), @Position int

 

SET @InList = 'yes,no,maybe'

 

SET @Position = 0

 

WHILE @Position <> 1

BEGIN

 

            SET @Position = CHARINDEX (',', @InList, @Position)

 

            IF @Position <> 0

            BEGIN

                        SET @InList = STUFF(@InList,@Position,1,''',''')

 

                        SET @Position = @Position + 2

            END

            ELSE

                        SET @Position = 1

END

 

SET @Query = '

SELECT *

FROM #ColumnValues

WHERE Value IN (''' + @InList + ''')'

 

PRINT @Query PRINT ''

 

EXEC (@Query)

 

In the above example the WHILE LOOP is used to add the single quotes needed to prepare the @InList variable for use in building our dynamic SQL.  Sometimes you can add the single quotes needed before passing the variable to a query or stored procedure that uses it to build a dynamic SQL query.  Again the PRINT lines are only there to show you how the query looks.

 

Example 5

 

Dynamically building the entire query based on values in various variables is what most database administrators and developers frown upon.  I get the feeling that this is the lazy developers way to develop instead of developing 10, 20, or more stored procedures and tune them to be as fast as possible.  However, if you are low on man power, it is easier to maintain and modify one stored procedure instead of 20 or more.  For this example you trade speed and better performance for convenience:

 

DECLARE @Query varchar(1000), @ClientName varchar(50), @ColumnName varchar (50),

@ColumnDisplay tinyint, @Debug bit

 

SET @ClientName = 'y'

SET @ColumnName = ''

SET @ColumnDisplay = 0

SET @Debug = 1

 

SET @Query = 'SELECT ClientName'

 

IF @ColumnDisplay > 1

            SET @Query = @Query + ',' + CHAR(10) + 'v.ColumnID'

 

IF @ColumnDisplay > 0

            SET @Query = @Query + ',' + CHAR(10) + 'Value'

 

IF @ColumnDisplay > 0

            SET @Query = @Query + ',' + CHAR(10) + 'ColumnName'

 

SET @Query = @Query + CHAR(10) + 'FROM #ColumnValues v'

 

IF @ColumnDisplay > 0 OR @ColumnName <> ''

            SET @Query = @Query + CHAR(10) + 'INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID'

 

SET @Query = @Query + CHAR(10) + 'WHERE v.ColumnID > 0'

 

IF @ClientName <> ''

            SET @Query = @Query + CHAR(10) + 'AND ClientName LIKE ''%' + @ClientName + '%'''

 

IF @ColumnName <> ''

            SET @Query = @Query + CHAR(10) + 'AND ColumnName = ''' + @ColumnName + ''''

 

IF @Debug = 1

            BEGIN

                        PRINT @Query PRINT ''

            END

 

EXEC (@Query)

 

DROP TABLE #ColumnNames

DROP TABLE #ColumnValues

 

There is a lot happening in this example.  Since those of you who actually copy and paste code to try it yourself I have included the DROP TABLE commands because for this example you’ll want to change the values of the four variables, @ClientName, @ColumnName, @ColumnDisplay, and @Debug, to see how changes affect how the query is built.  I normally add @Debug to queries and stored procedures with dynamic SQL so that I can see what they are building in a production environment without changing them and they’ll function normally for anyone else using them.

 

Some values you can try are:

@ColumnName = ‘dealer’

@ColumnDisplay = 1 and then = 2

@Debug = 0

@ClientName = ‘’

 

Conclusions

 

There are times when dynamic SQL is the easiest, possibly the only, solution to develop and can perform just as well as static SQL.  When performance and speed are a top priority then you should try to avoid dynamic SQL as much as possible.

 

I look forward to your comments and suggestions.

 

Total article views: 14035 | Views in the last 30 days: 5
 
Related Articles
FORUM

dynamic sql example

dynamic sql example

FORUM

avoiding dynamic sql for updating

update columnname based on a variable without using dynamic sql

FORUM

Dynamic Query in UDF?

Dynamic Query in UDF?

FORUM

CASE statement versus dynamic Query

CASE statement versus dynamic Query

FORUM
Tags
performance tuning    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones