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]

and has the been populated with the following sample data:

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

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

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

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

select	*
from	dbo.employees
where	(id = @employeeID or @employeeID is null)
	(firstName = @firstName or @firstName is null)
	(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:


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".

// //]]>