Published: Monday, October 23, 2000
Using Dynamic SQL Statements in Stored Procedures, Part 3
By Scott Mitchell
Read Part 1
Read Part 2
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!
By Scott Mitchell
