In this tutorial we'll look at using optional parameters in a SQL Stored Procedure. This is a question I get a lot, even at work. People want to know how they can have optional parameters in a stored procedure. Lets look at the scenario:
You have an application for the inventory/sales of your books for your bookstore. In your application you have a search form, there are several items a person can search by, lets they can search by Author, Title, ISDN, or Year. Now you have 2 options;
- Option 1: You can write 4 different stored procedures, then based on what the user wants to search by call that stored procedure.
- Option 2: You can write a single stored procedure and pass all the search parameters to your stored procedure and let your stored procedure decide what the user is searching by.
In the interest of maintainability and scalability I would go with
Option 2, it is far easier to maintain a single stored procedure than 4 of them. So now we're going to look at how to do this, how to have a single procedure and call it no matter what option the user decides to search by. Before getting to the actual process of optional parameters, I would like to pass along what I feel are
rules for good design.
First, when creating a stored procedure most people rush in and start
CREATE PROCEDURE...., then run it. Next time they go to edit it, if they don't open it via Query Analyzer or Enterprise Manager, they have to change the
CREATE PROCEDURE... to
ALTER PROCEDURE... so Ive always found it best to address this right from the start.
In all Microsoft SQL Servers there exists a table called
sysobjects, in here are all the objects within that server, the tables, stored procedures, user defined functions, etc. SO what I do is add a
header (for the lack of a better term) that checks this table to see if this procedure exists, if it doesnt then I create a
shell of the procedure, then right below I alter the same stored procedure.
Theres a catch though, either the words
CREATE or
ALTER must be the first word in a stored procedure, if its not then SQL will error out, so I do this with
Dynamic SQL.
Dynamic SQL is a term used to mean SQL code that is generated programmatically (in part or fully) by your program before it is executed. As a result it is a very flexible and powerful tool. You can use Dynamic SQL to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
This is how my
header looks:
CODE
DECLARE @SQL_PROC VARCHAR(4000)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'YourProcedureName')
BEGIN
PRINT 'Creating Procedure YourProcedureName'
SET @SQL_PROC = 'CREATE PROCEDURE dbo.YourProcedureName
@value1 CHAR(100) = NULL,@value2 CHAR(100) = NULL,@value3 CHAR(100) = NULL,@value4 CHAR(100) = NULL
AS
SELECT 1
GO'
EXEC(@SQL_PROC)
END
GO
Here is a breakdown of what is happening here
- I declare a variable named @SQL_PROC, this variable is holding the SQL code I wish to execute
- I then search sysobjects for a procedure with this same name, if it don't exist then I create a shell version. By a shell version I mean I create the stored procedure with the parameters I want, but it only contains SELECT 1, this gives me a valid stored procedure.
- I then call EXEC to execute the variable EXEC(@SQL_PROC). When this string is executed, its creating a stored procedure
Notice in the procedure declaration I have
@value1 CHAR(100) = NULL , and I do this for all the parameters. This sets their default value to NULL, because all the parameters are optional, we don't know which ones the user is going to pass our procedure.
Now that we have out
shell created, right after the last
GO statement we then put the rest of our stored procedure, except this time instead of
CREATE PROCEDURE we use
ALTER PROCEDURE. We can do this because in the first block of code we create this stored procedure if it doesn't already exist. The rest of the stored procedure looks like this:
CODE
ALTER PROCEDURE dbo.YourProcedureName
@value1 CHAR(100) = NULL,
@value2 CHAR(100) = NULL,
@value3 CHAR(100) = NULL,
@value4 CHAR(100) = NULL
AS
SELECT
column1,
column2,
column3,
column4
FROM
Table_Name
WHERE
((@value1 IS NULL) OR (column1 = @value1))
AND ((@value2 IS NULL) OR (column2 = @value2))
AND ((@value3 IS NULL) OR (column3 = @value3))
AND ((@value4 IS NULL) OR (column4 = @value4))
In this procedure the first 3/4 of the procedure is absolutely normal, SELECT something FROM some table WHERE, its after the
WHERE that things get different. As you can see we have 4 different items the user could have passed us, we just don't know which ones. To combat this, we have the where clause structured like so
CODE
((@value1 IS NULL) OR (column1 = @value1))
AND ((@value2 IS NULL) OR (column2 = @value2))
AND ((@value3 IS NULL) OR (column3 = @value3))
AND ((@value4 IS NULL) OR (column4 = @value4))
How does this work you ask? Well it hits the first line, if the parameter @value1 is NULL it does nothing, it skips to the next line. It does this until it reaches a parameter that isn't NULL, once it finds that, it jumps to the other side of the
OR statement and executes
column* = @value*. The way its structured, if the parameter is empty (NULL) then the first part of the statement is true, meaning that line is executed, until it finds a non-null parameter.
Now lets look at a different scenario, lets say (like above) you have several optional parameters, but the user can enter as many of either parameter they want, or multiples of all if they so choose to, how would you handle this? Well first you would have to use Dynamic SQL for the entire
SELECT portion of the procedure. The above approach wouldn't work with multiples of a parameter, i.e: Koontz,King,Rice if they searched by those 3 authors, so how to get that into the procedure?
Transact-SQL has a clause, the
IN clause, which allows for a statement like
WHERE authors IN ('Koontz','King','Rice'), but with regular SQL you cannot just pass a delimited string into the procedure and expect it to understand what you're doing. But if you do the while thing in Dynamic SQL, when the string is executed it will execute as above, like you did the
WHERE authors IN ('Koontz','King','Rice') from above.
So lets take a look at how you would create this stored procedure for this scenario (the header remains the same):
CODE
ALTER PROCEDURE dbo.YourProcedureName
@value1 CHAR(100) = '',
@value2 CHAR(100) = '',
@value3 CHAR(100) = '',
@value4 CHAR(100) = ''
AS
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'SELECT
column1,
column2,
column3,
column4
FROM
Table_Name
WHERE
((''' + @value1 + ''' = '''') OR (column1 IN(''' + @value1 + ''')))
AND ((''' + @value2 + ''' = '''') OR (column2 IN(''' + @value2 + ''')))
AND ((''' + @value3 + ''' = '''') OR (column3 IN(''' + @value3 + ''')))
AND ((''' + @value4 + ''' = '''') OR (column4 IN(''' + @value4 + ''')))'
EXEC(@SQL)
In this scenario we change a few things, the default values for the parameters is now '' (empty string) not NULL, then in the WHERE clause we check if the @value* parameter is equal to '' (empty string) instead of checking if its NULL.When the
@SQL variable string is executed with the parameters in place, it looks just like you had
WHERE authors IN ('Koontz','King','Rice') hard coded in your stored procedure, thus SQL knows what to do with it.
The downside of Dynamic SQL is twofold. First, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose the performance boost that you usually gain with stored procedures. The other downside is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in your application or website.
The advantage is, of course, that you are able to achieve a flexibility in your code that you can not get with standard SQL. I tend to use Dynamic SQL for scenarios such as the one above, it makes it simpler to accomplish what I'm looking for. Well thats how to use optional parameters in your SQL stored procedure. Thanks for reading
Happy Coding!
Optional Parameters in MSSQL
29 Aug, 2007 - 08:14 PM
| 