Introduction

Let me begin by defining a dynamic stored procedure as a stored procedure that builds a query or series of queries in a variable or variables of varchar and executes them at the end. There are many ways you can do this. In this article I show you the way I currently design them and the reasons why I code the way I do when designing this type of stored procedure. I do not intend to say that you should or should not use what I show here. There are people that will argue for or against building queries in variables and then executing them. My intent is to show you a good way to develop dynamic stored procedures should you decide you want to.

Planning

You will find it best to spend a little more time planning how you will design this stored procedure than you would if you designed one that just executes static SQL. 

Your first concern should be how many characters are likely to be used to create the finished code. A varchar variable can only be declared with a max of 8,000 characters. So, if your code could be larger than 8,000 characters you will need more than one such variable. If it will be more than 16,000 characters you will need more than two varchar 8,000 variables and so on.

Next you will need to ensure proper permissions are given. Code in a variable that is executed by the command EXEC does not take advantage of ownership chains. This means that all users must have permissions to execute the stored procedure and have permissions to view and or modify data in the tables that are used in the stored procedures. This is one of the reasons many people argue against executing code in a variable.

Keep in mind that when you build a query in a variable SQL Server can’t check the syntax of the query until it is actually executed. You should plan some extra time for testing since you will need to execute every possible query that could be built to make sure none are built with syntax errors.

The last extra concern you must be aware of is to ensure that no unauthorized code is executed by one of these stored procedures. If you fail to put a simple check in place you leave your data vulnerable to unauthorized viewing and/or modification. I will show this technique below as I describe how I design dynamic stored procedures.

Format

Format 1

I have used various different formats (or techniques) to fill a variable for execution. The first one is as follows:

DECLARE @Query varchar(500)

SET @Query = ''

SELECT @Query = @Query + 'SELECT Column1,

Column2,

Column3

FROM table1'

PRINT @Query

This format makes the query easy to read when you view it. However, the code itself won’t always be so easy to read.

Format 2

I found a way to make the code easier to read as well as make the query easy to read. Here is the reworked code:

DECLARE @Query varchar(500)

SET @Query = ''

SET @Query = @Query 

+ 'SELECT Column1,' + CHAR(10) + CHAR(9) 

+ 'Column2,' + CHAR(10) + CHAR(9) 

+ 'Column3' + CHAR(10) 

+ 'FROM table1'

PRINT @Query

The output of this code is the same as in format 1. This time the code, to me, is also lined up nicely and easier to read. Please note that if you copy and paste this code into Query Analyzer it is lined up correctly; other places to store this code will handle tabs and spaces differently. Word and Enterprise Manager are among those other places mentioned. Notice I changed the word SELECT to SET. SELECT will work, however it is not required and is associated more with displaying data and since I am not displaying data I prefer to use the SET command.

Format 3

The way I use most often to develop dynamic stored procedures is between the two formats shown above:

DECLARE @Query varchar(500)

SET @Query = ''

SET @Query = @Query 

+ 'SELECT Column1'

+ ',' + CHAR(10) + CHAR(9) + 'Column2'

+ ',' + CHAR(10) + CHAR(9) + 'Column3' 

+ CHAR(10) + 'FROM table1'

PRINT @Query

The above code doesn’t always look as good as format 2, but it is good when you have to insert more code or move lines of code as needed. It is also designed to make it least likely to generate syntax errors as you modify how the query is built. You’ll notice I moved the comas from immediately behind the column to the line below the column. The query still looks the same as the previous two formats, however if I were to add an IF statement to not add the last column until a certain condition were met I wouldn’t have to remember to remove the end coma from the second column. This modification will be discussed more in example 2. For these reasons I prefer to use format 3 when designing dynamic stored procedures. The below two examples will use format 3 to build a query in a variable.

Example 1

Now I will show you how a simple dynamic stored procedure could look if I were to develop it. First here is the code you will need to run if you plan to execute the stored procedure:

CREATE TABLE Names (NameID int NOT NULL IDENTITY (1,1), LastName varchar(30))

GO

CREATE INDEX [IX_LastName] ON [dbo].[Names]([LastName]) ON [PRIMARY]

GO

INSERT INTO Names (LastName)

SELECT 'Marda' UNION SELECT 'Bakker' UNION SELECT 'Martinez' UNION SELECT 'Anderson'

Now here is the stored procedure with comments the way I would place them:

/*

Created by: Robert W Marda

When created: 18 March 2003

Purpose: Demonstrate a simple dynamic stored procedure 

*/

CREATE PROCEDURE spExample1

(

@LastName varchar(30) = '',

@NameID int = 0,

@Debug bit = 0

) AS

--The below IF statement will check for invalid parameter combinations and exit the SP if found.

IF @LastName <> '' AND @NameID <> 0

BEGIN

PRINT 'You cannot use @LastName and @NameID together in this stored procedure'

RETURN

END

DECLARE @Query varchar(500)

--The below line is needed to ensure no one can execute unauthorized code when this SP is executed.

SET @LastName = REPLACE (@LastName, '''', '''''')

--build SELECT and FROM clause

SET @Query = 'SELECT LastName'

+ ',' + CHAR(10) + CHAR(9) + 'NameID'

+ CHAR(10) + 'From Names'

--Begin building WHERE clause

IF @LastName <> '' OR @NameID <> 0

SET @Query = @Query

+ CHAR(10) + 'WHERE '

IF @LastName <> ''

SET @Query = @Query

+ 'LastName LIKE ''' + @LastName + '%'''

IF @NameID <> 0

SET @Query = @Query

+ 'NameID = ' + LTRIM(STR(@NameID))

--End building WHERE clause

IF @Debug = 1

PRINT @Query

EXEC (@Query)

Let me explain a few things about the above stored procedure. First you will notice a variable called @Debug. I always give this variable a default value of 0 which will ensure none of the code I use to debug problems with the SP will execute unless I send in a value for @Debug. For such a simple stored procedure I only need one line for debugging purposes and that line will display the entire code that was built in the variable @Query just before that code is executed. I always print the entire code so that when an SP doesn’t work as it should I can see what it is actually doing.

The REPLACE function used in the SP protects against users trying to execute their own code when this SP runs. If you want to see how someone can execute their own code remark out the line with the REPLACE function in it and then execute this code:

EXEC spExample1

@LastName = ''' delete from Names--',

@NameID = 0,

@Debug = 1

After it executes you will notice that two queries were executed. The first query is the one that is supposed to be run by the SP and it returns no records. The second query is the unauthorized one found in the variable @LastName and it deleted all the rows in table Names; unless you were using an account that only had SELECT or read permissions to the table Name in which case the DELETE statement will not be executed because DELETE permissions were not granted. Now modify the SP again to unremark the REPLACE function and re-execute the same SP call above and see how it handles this unauthorized code. As long as you always remember to replace all single quotes with two single quotes you will never have a problem of someone executing unauthorized code while executing on of your stored procedures.

You will also notice that this SP is designed to only handle one of the two possible parameters that can be sent in. Because of this there is code at the beginning that will cause the SP to stop execution before it can build the code it should execute and thus avoiding an error. The next example will show you how to create an SP that can handle both, one, or none.

Example 2

Now we’ll look at a dynamic stored procedure that is a little more complex. This one will be able to handle all or none of the parameters and will be able to build code that exceeds 8,000 characters. We’ll use the same table as in example 1:

/*

Created by: Robert Marda

When created: 21 Mar 2003

Purpose: Demonstrate a simple dynamic stored procedure 

*/

CREATE PROCEDURE spExample2

(

@LastName varchar(30) = '',

@NameID int = 0,

@Debug bit = 0

) AS

DECLARE @SelectClause varchar(8000),

@FromClause varchar(8000),

@WhereClause varchar(8000)

--The below line is needed to ensure no one can execute unauthorized code when this SP is executed.

SET @LastName = REPLACE (@LastName, '''', '''''')

--Begin building SELECT clause

SET @SelectClause = 'SELECT LastName'

IF @NameID <> 0

SET @SelectClause = @SelectClause

+ ',' + CHAR(10) + CHAR(9) + 'NameID'

--End building SELECT clause

--Begin building FROM clause

SET @FromClause = CHAR(10) + 'From Names'

--End building FROM clause

--Begin building WHERE clause

IF @LastName <> '' OR @NameID <> 0

SET @WhereClause = CHAR(10) + 'WHERE '

ELSE

SET @WhereClause = ''

IF @LastName <> ''

SET @WhereClause = @WhereClause

+ 'LastName LIKE ''' + @LastName + '%'''

IF @LastName <> '' AND @NameID <> 0

SET @WhereClause = @WhereClause

+ CHAR(10) + 'AND '

IF @NameID <> 0

SET @WhereClause = @WhereClause

+ 'NameID = ' + LTRIM(STR(@NameID))

--End building WHERE clause

IF @Debug = 1

BEGIN

PRINT @SelectClause

+ @FromClause

+ @WhereClause 

+ CHAR(10)

END

EXEC (@SelectClause + @FromClause + @WhereClause)

First you will notice that this stored procedure uses three variables to store sections of the query being built. In example 1 only one variable was used. The stored procedure in example 2 can handle 24,000 characters of code where as the 1st example could only handle 8,000.

The WHERE clause can handle one, both, or none of the search criteria. Be aware that this style of generating a WHERE clause becomes (in my opinion) too hard to maintain and read if used for more than a few conditions. When you must handle many search criteria and don’t know which of them will or won’t be used you can simply make sure you have one that will always be there and if you don’t have one then you can use “1=1” after the keyword WHERE.

You will also notice that it was a simple matter of inserting a few blank lines between the two columns in the SELECT clause and adding an IF statement to control when one of the columns is included or excluded from the SELECT list.

Another thing to keep in mind is that if you don’t trap for the percent sign then a user entering it before the first character can change your starts with like search to an any like search. That can be important if you want to take advantage of indexes since SQL Server will have to scan the entire index or table to find all matches for an any like search. Execute this code to do an any like search using spExample2:

EXEC spExample2

@LastName = '%a',

@NameID = 0,

@Debug = 1

If you don’t want to allow this then add the following line of code to the above stored procedure:

SET @LastName = REPLACE (@LastName, '%', '')

When you rerun the above stored procedure call you will get only one record and if you look at the execution plan you will see that SQL Server used an index seek instead of an index scan.

Conclusions

In this article I have show you a few ways to develop dynamic stored procedures. I have discussed why I prefer one format over the others and why I use some of the code I use in my dynamic stored procedures. I have discussed how to ensure unauthorized code can never be executed while executing the queries built in dynamic stored procedures. In a future article I plan to discuss more complex dynamic stored procedures.