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.