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) and (firstName = @firstName or @firstName is null)
and (lastName = @lastName or @lastName is null)
So it not compulsory to pass these parameters to SP.....
Using optional parameters can come in handy for a number of situations:
- When a parameter is missing, assign it a default value. You could do that through the parameter declaration itself (e.g. @MyNumber INT = 1), or you might use a complex algorithm within the procedure to select the default value.
- When a parameter is missing, return a specific error code that has special meaning to the calling procedure, so it can take appropriate action. My example procedure below demonstrates this approach.
- When a parameter is missing, branch to specific logic within the stored procedure.