Search

Monday, January 14, 2008

Using Optional Parameters in SQL Server Stored Procedures

Stored procedures are a nice way to encapsulate some business logic inside your database. This post is not about learning how to write stored procedures but instead deals with the specifics of implementing optional parameters in your SQL Server stored procedures. You can learn more about stored procedures at Wikipedia or Google.

The most common use of stored procedures is to return some data based on some passed in parameters. More often then not they need to take multiple parameters. A common struggle is how to pass optional parameters to a stored procedure.

Let's talk about a simple example. You have an employees table that has the employee id, first name and last name. The table is defined as follows:

create table [dbo].[employees] (
  [id] int identity(1, 1) not null,
  [firstName] varchar(255) null,
  [lastName] varchar(255) null,
  primary key clustered ([id])
)
on [primary]
go

and has the been populated with the following sample data:

insert into [dbo].[employees] ([firstName], [lastName])
values (N'Boyan', N'Vassilvev')
go

insert into [dbo].[employees] ([firstName], [lastName])
values (N'Boyan', N'Kostadinov')
go

insert into [dbo].[employees] ([firstName], [lastName])
values (N'Doug', N'Boude')
go

You would like to write a stored procedure to return this data but if you don't want to write separate procedure for each type of employee search you want to execute. Ideally, you want to write one stored procedure that will return all employees or only an employee with a certain id or an employee has a certain first name (or last name for that matter). So how is this done? Simple, you need to use optional parameters in your stored procedure. You define your procedures as follows:

create	proc	dbo.spGetEmployees
	@employeeID int = null,
	@firstName varchar(255) = null,
	@lastName varchar(255) = null
as

select	*
from	dbo.employees
where	(id = @employeeID or @employeeID is null)
	and
	(firstName = @firstName or @firstName is null)
	and
	(lastName = @lastName or @lastName is null)

Now you can call the same stored procedure 4 different ways:

-- Without parameters to get all the employees
exec dbo.spGetEmployees
-- With id parameter to get an employee with a specific id
exec dbo.spGetEmployees 1
-- With first name parameter to get an employee with a specific first name
exec dbo.spGetEmployees null, 'boyan'
-- With last name parameter to get an employee with a specific last name
exec dbo.spGetEmployees null, null, 'kostadinov'

And you will get results which look like:

image

Note: Something to keep in mind is that the parameter order is important. If you are specifying only the first optional parameter, you do not need anything else. However, if you want to use any but the first parameter, you need to set any preceding parameters to "null".

17 comments:

  1. Great post, I was trying to use IsNull or Coalescence but this was exactly what I needed, because I want to return also the null rows.

    ReplyDelete
  2. That post hit the nail write in the head for me. You explain it so concise and exact. You started from the creation of the table so we can see how the table was structured, the creation of the stored procedure and print screened examples of every possible way to call that SP. Great JOB!

    ReplyDelete
  3. Thank you. I've been looking all over the Net for this simple solution and until now it didn't make sense. Your wording is perfect!

    ReplyDelete
  4. Good idea (I also used it) but with some restrictions: What do you do, if you want to search for real NULL values? Frankly and sadly, it's not possible with that approach because if the parameter is NULL it will be ignored. Also if you want to search for ranges you are forced to add parameters (eg startDate, endDate, ...) to search for a timeframe as well as for an exact date/time.

    ReplyDelete
  5. Marcus,
    For date ranges vs. exact date, you can use the start date parameter to specify the exact date and don't specify a value for the end date parameter. Then if the end date is null, set it equal to the start date. You shouldn't have to change your BETWEEN clause.

    ReplyDelete
  6. U could say that this technique has a serious performance cost. Even using indexes when u use IS NULL OR server does a full table scan...

    ReplyDelete
  7. There's a performance cost, but generally when I see this sort of thing I see folks generating dynamic SQL inside the of sproc to account for the optional parameters, so clearly this technique is preferred over that. Thanks for writing up the article, I was trying to explain this technique to an analyst and found that you did a much better job in your article than I did in my emails :D

    ReplyDelete
  8. Thanks. I've searched all over the net for that simple solution, and no one of the other solutions worked. Yours went smoothly. Now, I don't have to write 5 stored procedures for accessing a single table.
    I thank you for that. More grease to your elbows.

    ReplyDelete
  9. Nice article with an example. Thank you.

    ReplyDelete
  10. Thanks man !! Its awesome ... great keep going and post some more ....

    ReplyDelete
  11. Great!!! Saved me a lot of searching.... Thanks a million!

    ReplyDelete
  12. I'd like to extend this a little to search for partial matches. For example, I have a simple reference table which will never contain a large number of rows so I'm not worried about performance in this instance. I tried creating a parameter which then searched for LIKE but the result was as though I hadn't entered any parameters at all. Any tricks to achieving this?

    ReplyDelete
  13. Though there is no problem in you query, This is more readable..

    Select *
    from dbo.employees
    where
    (ID = CASE
    WHEN @employeeID IS NULL THEN ID
    ELSE @employeeID END)
    and
    (firstName = CASE
    WHEN @firstName IS NULL THEN firstName
    ELSE @firstName END)
    AND
    (lastName= CASE
    WHEN @lastName IS NULL THEN lastName
    ELSE @lastName END)

    ReplyDelete

// //]]>