Post
Ask Question

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,Name) And  
  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.
Next Recommended Reading Insert XML Data In Database Using Stored Procedure In SQL Server