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'
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.
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.
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.
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.
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 = ''
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.