This article assumes that you know what a stored procedure is, and have had experience creating them and executing them via an ASP page. If this is not the case, be sure to read the following articles first:

- continued -


• Writing a Stored Procedure
  
• Writing a Stored Procedure Part II

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 (see How to Deal with Apostrophes in your SQL String for more details).

For a good application of using the EXEC statement, be sure to read: Using the IN Notation in Stored Procedures; for a thorough discussion on the efficiency of dynamic stored procedures, read The Speed of Dynamic Queries.

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.

Happy Programming!