How To Pass Optional Parameters in SQL Server Stored Procedure.

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 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'

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s