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:
Or perhaps you'd like to be able to query a particular table based upon a parameter, like:
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 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:
The second example could be changed to:
Note that you have to surround the value of
@LastName with single
quotes, much like you do when building a SQL statement in an ASP page. Also note that if
@LastName contain single quotes, an error will occur. Therefore, you
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:
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.