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:
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:
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
((@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):
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