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.