Using Dynamic SQL Statements in Stored ProceduresBy 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:
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
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
- 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,
EXECUTE), which can be used to dynamically execute a
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:
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
LastName column contained the string
mitchell. You could edit the above code to:
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