Using Dynamic SQL Statements in Stored Procedures, Part 3

In Read Part 2 we looked at creating our first truly dynamic SQL statement within a stored procedure, and we examined how to call it from an ASP page. Now... you may be wondering why are we going to such painful lengths to do it this way for just one variable in a WHERE clause (especially since you can do it without dynamic SQL and a stored procedure parameter, like:

CREATE PROCEDURE sp_MyFirstDynamicSP ( @LastName varchar(100) ) AS SELECT FirstName, LastName, SSN, Salary FROM Employee WHERE LastName = @LastName

Well, as shown by the above example, it really doesn't make sense to use dynamic SQL in a stored procedure to just return rows based on one value in a WHERE clause. But imagine that you wanted to do more... like order the results based on a column that might change everytime! For example, say that in our Employee results, sometimes we wanted to order by LastName and then FirstName, while other times we want to order by Salary. This can be done in a stored procedure via dynamic SQL as well! Let's make a small change to our sp_MyFirstDynamicSP procedure to include a second parameter, @SortColumn.

CREATE PROCEDURE sp_MyFirstDynamicSP ( @EmployeeID varchar(100), @SortColumn varchar(100) ) ...

Now, we can alter our dynamic SQL statement to create an ORDER BY clause that is based on the value of @SortColumn:

CREATE PROCEDURE sp_MyFirstDynamicSP ( @EmployeeID varchar(100), @SortColumn varchar(100) ) EXEC('SELECT FirstName, LastName, SSN, Salary FROM Employee WHERE LastName LIKE ''%' + @LastName + '%'' ORDER BY ' + @SortColumn )

From the ASP page, you want to pass in the column list that you wish to sort by. For example, if you wanted to list all employee's with a lastname containing the text mitchell and sort those results by the value of the Salary column in descending order, you'd call the stored procedure like so:

Dim strSQL strSQL = "sp_MyFirstDynamicSP '" & Request("LastName") & "', 'Salary DESC' "

If you wanted to sort by LastName and break ties by sorting on FirstName, you'd call the stored procedure like:

Dim strSQL strSQL = "sp_MyFirstDynamicSP '" & Request("LastName") & "', 'LastName, FirstName' "

I'll leave you with some advice from avid 4Guys visit Leo C., who shares his views on dynamic SQL in stored procedures:

Here are some things to consider when deciding whether to use dynamic SQL in stored procedures:
1. This only works with MS SQL Server, not Oracle or any version of Sybase that I have used.
2. It is not sufficient to give execute permissions on the procedure to users, you have to grant permissions on the table(s).
3. In spite of these problems, you can use this technique to handle, for example, 100 different medical practices, each with their own separate set of tables. The exec function will allow you to concatenate the different table names from variables, as long as the table names are identical except for some enumeration, such as the practice number.

Happy Programming!