Optional parameters in SQL Server Stored Procedures

In this blog, I have explained how to make optional parameters by specifying default values in SQL Server Stored Procedures.

To demonstrate the above concept I will use the following table:

CREATE TABLE Mas_Employee
(
Id int IDENTITY PRIMARY KEY,
Name nvarchar(50),
Gender nvarchar(50),
Salary int,
DeptId int
)

Here is Mas_Employee table, now use the following scripts:

Insert into Mas_Employee ( Name, Gender, Salary, DeptId )
Select 'Jaipal', 'Male', 18200, 1 Union All
Select 'Jayanth', 'Male', 12200, 2 Union All
Select 'Sreeshanth', 'Male', 12999, 2 Union All
Select 'Sujit', 'Male', 8000, 3 Union All
Select 'Tejaswini', 'Female',16800, 1 Union All
Select 'Akhil', 'Male', 10000, 5 Union All
Select 'Jayalalitha','Female',8000, 4 Union All
Select 'Deepak', 'Male', 12999, 2 Union All
Select 'Arun', 'Male', 15000, 1

Now create stored procedure with optional parameters.

--Exec USP_SearchEmployees NULL,'Male',1
Create Procedure USP_SearchEmployees
@Name nvarchar(50) = NULL,
@Gender nvarchar(50) = NULL,
@DeptId int = NULL
As
Begin
Select * from Mas_Employee
Where
Name = ISNULL(@Name,Name) And
Gender = ISNULL(@Gender,Gender) And
DeptId = ISNULL(@DeptId,DeptId)
End

In the stored procedure, the following are the optional parameters: Name, Gender and DeptId. Notice that, we have set defaults for all the parameters: Name, Gender and DeptId, and in the "WHERE" clause we are checking if the respective parameter IS NULL.

Test : Test the stored procedure by executing the following statements.

Exec USP_SearchEmployees
-- It returns all the employess
Exec USP_SearchEmployees 'Jaipal'
-- It returns employess whose name is 'Jaipal'
Exec USP_SearchEmployees NULL,'Male'
-- It returns all 'Male' employess
Exec USP_SearchEmployees NULL,'Male',1
-- It returns all 'Male' employess whose DeptId is 1

I hope you enjoyed it. please provide your valuable feedback and suggestions if you found this article is helpful.

4 Comments

Join the conversation! Your thoughts help the community grow.

Janakiram Jan4y ago
If we specify default values for the parameters then they are optional parameters. Thanks for the information.
Anijim James-Maurice5y ago
Nice use of ISNULL function. The only con is that you have to specify NULL if you want to skip the first parameter. If that parameter could be skipped completely, the function would be perfect. Thanks all the same ??
Jaipal Reddy10y ago
Thank you Subash
Subash10y ago
Nice one