Published: Monday, October 23, 2000
Using Dynamic SQL Statements in Stored Procedures
By Scott Mitchell
We've all likely created ASP pages that contain dynamic SQL statements within the ASP pages... SQL
statements that are constructed on the fly, its parameters based upon, usually, user input. For example,
imagine that you had a database table that stored information on your company's employees. Perhaps you'd want
to add some interface that allowed users to enter an employee's last name and have an ASP page that returned
a list of employees with that last name. You would start by creating a form that allowed the user to
enter a last name, redirecting the submitted form to an ASP page that built a dynamic SQL query based on the
user's input. The code to build the dynamic SQL might look like:
Dim strSQL
strSQL = "SELECT FirstName, LastName, SSN, Salary" & _
"FROM Employee " & _
"WHERE LastName LIKE '%" & Request("LastName") & "%'"
|
Such dynamic SQL statements are, in my opinion, ugly, error-prone, and difficult to maintain. What happens if
a number of pages need to use this dynamic SQL statement? Are you going to cut and paste it into each page?
And if so, what happens when the Employee table is altered, and the Salary column
is renamed or removed? You are going to be very busy pecking through you many ASP pages looking for those
where you used this clip of dynamic SQL!
Dynamic SQL is also hard to read, especially if you have tons of ASP variables inserted into the string,
or you build up the string on a number of conditional statements. I've been guilty of creating quite lengthy
and horribly unreadable dynamic SQL strings, strings that were created over fifty or so lines of code, being
very dynamic, involving an assortment of Select Case and If ... Then ... Else statements!
Whenever I need to access a SQL Server database through an ASP page, I like to use a stored procedure for a number
of reasons. (If you are unfamiliar with what, exactly, stored procedures are, or how to create them, I highly
suggest you read Nathan Pond's article: Writing a Stored Procedure.)
Stored procedures have the following advantages over in-script SQL statements:
- Performance - stored procedures, when executed, store an execution plan for speedier execution of future
calls.
- Stored procedure calls reduce client/server network traffic (rather than sending
SELECT columnList
FROM TableName WHERE ... you send simply the stored procedure name and its parameters (if any).
- Stored procedures isolate business rules
- Stored procedures help in code modularization
- Isolates the ASP page(s) from changes to the database (as aforementioned, when the data model changes, what
would you rather do: alter SQL statements in several ASP pages, or just alter a single stored procedure?)
Now, you may be thinking, "Yeah, I usually like to use stored procedures, but I need to execute dynamic
SQL statements." Well, you can use dynamic SQL statements within a stored procedure! Using dynamic SQL in
stored procedures does reduce one advantage of stored procedures: performance. What good's an execution plan
when the stored procedure is dynamic? (For more on this topic, be sure to read: The
Speed of Dynamic Queries!) However, there are still a number of other advantages that stored procedures
offer, and hence, I argue, it is still worth creating stored procedures to handle dynamic SQL.
That being said, let's look at how we can use dynamic SQL statements within a stored procedure! SQL contains
a nice command, EXEC (or EXECUTE), which can be used to dynamically execute a
SQL statement. EXEC expects a string - the SQL statement you wish to execute - and then goes off,
executes the passed in SQL statement, and returns the results. A very simple version of this in a
stored procedure would be:
CREATE PROCEDURE sp_MyFirstDynamicSP
AS
EXEC('SELECT FirstName, LastName, SSN, Salary FROM Employee')
|
Of course, this isn't a very practical use of the EXEC statment, since the SQL is not dynamic at
all! Why not just hard code SELECT FirstName, LastName, SSN, Salary FROM Employee? Before we look at making the above stored
procedure dynamic, imagine for a moment that you wanted to return only those rows in Employee
where the LastName column contained the string mitchell. You could edit the above code to:
CREATE PROCEDURE sp_MyFirstDynamicSP
AS
EXEC('SELECT FirstName, LastName, SSN, Salary
FROM Employee
WHERE LastName LIKE ''%mitchell%'' ')
|
Note that I used the double apostrophes to denote a single apostrophe in the dynamic SQL string. This pair
of apostrophes creates a single, literal apostrophe in the dynamic SQL string...
At this point we've not added any value to our stored procedure. After all, we simply have a hard-coded SQL
statement being executed by the EXEC statement! How silly! In Part 2
we'll look at extending our stored procedure to use user-inputted values in the EXEC statement!
Read Part 2!
