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.
