Running Dynamic Stored Procedures

Now, one question that is commonly asked is, "How to I create a stored procedure that can execute dynamic SQL statements?" For example, you may want a stored procedure that takes, as an input, a particular WHERE clause, such that your stored procedure could be defined as:

                    CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
                    AS
                        SELECT *
                        FROM TableName
                        WHERE @WHEREClause
                    

Or perhaps you'd like to be able to query a particular table based upon a parameter, like:

                    CREATE PROCEDURE MyProc
                      (@TableName varchar(255),
                       @FirstName varchar(50),
                       @LastName varchar(50))
                    AS
                        SELECT *
                        FROM @TableName
                        WHERE FirstName = @FirstName
                          AND LastName = @LastName
                    

In either case, if you try putting either of the above code snippets into a stored procedure, you'll get an error. To execute a dynamic SQL statement in a stored procedure, you need to use the EXEC function. The EXEC function takes a SQL string as a parameter, and executes that SQL statement.

So, when using the EXEC function, begin by declaring a varchar(255) variable named @SQLStatement. Then, assign your dynamic SQL statement to this variable, and, finally, use EXEC to execute the SQL statement! For example, the first example above should be changed to:

                    CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
                    AS
                    
                        -- Create a variable @SQLStatement
                        DECLARE @SQLStatement varchar(255)
                        
                        -- Enter the dynamic SQL statement into the
                        -- variable @SQLStatement
                        SELECT @SQLStatement = "SELECT * FROM TableName WHERE "
                                                  + @WHEREClause
                        
                        -- Execute the SQL statement
                        EXEC(@SQLStatement)
                    

The second example could be changed to:

                    CREATE PROCEDURE MyProc
                      (@TableName varchar(255),
                       @FirstName varchar(50),
                       @LastName varchar(50))
                    AS
                    
                        -- Create a variable @SQLStatement
                        DECLARE @SQLStatement varchar(255)
                        
                        -- Enter the dynamic SQL statement into the
                        -- variable @SQLStatement
                        SELECT @SQLStatement = "SELECT * FROM " +
                                       @TableName + "WHERE FirstName = '"
                                       + @FirstName + "' AND LastName = '"
                                       + @LastName + "'"
                        
                        -- Execute the SQL statement
                        EXEC(@SQLStatement)
                    
                    

Note that you have to surround the value of @FirstName and @LastName with single quotes, much like you do when building a SQL statement in an ASP page. Also note that if @FirstName or @LastName contain single quotes, an error will occur. Therefore, you should Replace the single quotes with two single quotes in your ASP page before calling the stored procedure.

For a good application of using the EXEC statement, be sure to read: Using the IN Notation in Stored Procedures;

CREATE PROC Foo
                      @stringpassed
                    AS
                    BEGIN
                      DECLARE @SQL varchar(100)
                      SET @SQL = 'SELECT * FROM table WHERE idno IN ' + @stringpassed
                    
                      EXEC(@SQL)
                    END
                    

• Reduces client-server network traffic (SELECT blah blah blah blah blah blah... vs. EXEC sp_foo)
  
• helpful for isolating business rules
  
• helpful for modularizing code and setting security
  
• helps isolate the application from schema changes (modifying an SP is a WHOLE lot easier than modifying and recompiling and redistributing an application.)

So, when you're making the decision between SP and non SP, remember that there are other reasons besides parse and compile time.

Also some things to consider, as noted by alert 4Guys reader Leo C.:

I have also used the EXEC function to be able to have dynamic stored procedures and found it very useful.

However, you must make clear that this only works with Microsoft SQL Server and not with Sybase or Oracle! I have tested this extensively, and this conventient feature is only available within MS SQL Server.

Also, I set the ODBC driver to ignore ANSI quotes, etc., although a different combination of doubel and single quotes may make this unnecessary.

Back to Index Page