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:

  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 )  
  2.   
  3. Select 'Jaipal''Male', 18200, 1 Union All  
  4.   
  5. Select 'Jayanth''Male', 12200, 2 Union All  
  6.   
  7. Select 'Sreeshanth''Male', 12999, 2 Union All  
  8.   
  9. Select 'Sujit''Male', 8000, 3 Union All  
  10.   
  11. Select 'Tejaswini''Female',16800, 1 Union All  
  12.   
  13. Select 'Akhil''Male', 10000, 5 Union All  
  14.   
  15. Select 'Jayalalitha','Female',8000, 4 Union All  
  16.   
  17. Select 'Deepak''Male', 12999, 2 Union All  
  18.   
  19. Select 'Arun''Male', 15000, 1  

Now create stored procedure with optional parameters.

  1. --Exec USP_SearchEmployees NULL,'Male',1  
  2.   
  3. Create Procedure USP_SearchEmployees  
  4.   
  5. @Name nvarchar(50) = NULL,  
  6.   
  7. @Gender nvarchar(50) = NULL,  
  8.   
  9. @DeptId int = NULL  
  10.   
  11. As  
  12.   
  13. Begin  
  14.   
  15. Select * from Mas_Employee  
  16.   
  17. Where  
  18.   
  19. Name = ISNULL(@Name,NameAnd  
  20.   
  21. Gender = ISNULL(@Gender,Gender) And  
  22.   
  23. DeptId = ISNULL(@DeptId,DeptId)  
  24.   
  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  
  2.   
  3. -- It returns all the employess  
  4.   
  5. Exec USP_SearchEmployees 'Jaipal'  
  6.   
  7. -- It returns employess whose name is 'Jaipal'  
  8.   
  9. Exec USP_SearchEmployees NULL,'Male'  
  10.   
  11. -- It returns all 'Male' employess  
  12.   
  13. Exec USP_SearchEmployees NULL,'Male',1  
  14.   
  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.