Published: Sunday, February 06, 2000
Running Dynamic Stored Procedures
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:
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!
