Optional Parameters in SQL Stored Procedures

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 insert with the following sample data:

INSERT INTO [dbo].[employees] ([firstName], [lastName])
VALUES (N'Ravi', N'Kumar'),
(N'Ravi', N'Kishore'),
(N'Mohan', N'Varma')
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). To get records 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
BEGIN
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)
END

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, 'Ravi'
-- With last name parameter to get an employee with a specific last name
EXEC dbo.spGetEmployees null, null, 'Kishore'

Note: 

The parameter order is important. If you are specifying only the first optional parameter, you need not to do anything else. If you want to pass second and  then pass first parameter as null as shown above script.