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:

  1. CREATE TABLE Mas_Employee
  2. (
  3. Id int IDENTITY PRIMARY KEY,
  4. Name nvarchar(50),
  5. Gender nvarchar(50),
  6. Salary int,
  7. DeptId int
  8. )
Here is Mas_Employee table, now use the following scripts:

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

Now create stored procedure with optional parameters.

  1. --Exec USP_SearchEmployees NULL,'Male',1
  3. Create Procedure USP_SearchEmployees
  5. @Name nvarchar(50) = NULL,
  7. @Gender nvarchar(50) = NULL,
  9. @DeptId int = NULL
  11. As
  13. Begin
  15. Select * from Mas_Employee
  17. Where
  19. Name = ISNULL(@Name,Name) And
  21. Gender = ISNULL(@Gender,Gender) And
  23. DeptId = ISNULL(@DeptId,DeptId)
  25. 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.

  1. Exec USP_SearchEmployees
  3. -- It returns all the employess
  5. Exec USP_SearchEmployees 'Jaipal'
  7. -- It returns employess whose name is 'Jaipal'
  9. Exec USP_SearchEmployees NULL,'Male'
  11. -- It returns all 'Male' employess
  13. Exec USP_SearchEmployees NULL,'Male',1
  15. -- 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.