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!

Article Information
Article Title:	Using Dynamic SQL Statements in Stored Procedures
Article Author:	Scott Mitchell
Published Date:	Monday, October 23, 2000
Article URL:	http://www.4GuysFromRolla.com/webtech/102300-1.shtml