SQL Server: An optimal way to create procedure with multiple optional parameters

Problem:

I took the Problem-Solution approach to write this post to keep it short and simple.

Here the problem I am talking about is the scenario when you have to write a stored procedure in SQL Server with multiple search options/parameters then you may use some IF…ELSE .. statements to do it. Let’s take an short example.

Example:

CREATE PROCEDURE [dbo].[getEmployeeList]
(
 @DeptId INT=NULL,
 @OfficeId INT=NULL
)
AS
BEGIN
 IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE DeptId=@DeptId AND OfficeId=@OfficeId
 END
 ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE DeptId=@DeptId
 END
 ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE OfficeId=@OfficeId
 END
 ELSE
 BEGIN
 SELECT *
 FROM dbo.Employees
 END
END

Here we can have 4 combinations of search options.

Now, if we want add some more parameters like SectionId,DesignationId,CityId etc. to this stored procedure then it will be a tough task to manage it and may end-up with a 100′s lines of script.

Solution:

I found a solution for this problem by creating multiple conditions for a single WHERE… tag. Now, if I implement it to the previous example then the stored procedure will be like the below script-

CREATE PROCEDURE [dbo].[getEmployeeList]
(
 @DeptId INT=NULL,
 @OfficeId INT=NULL
)
AS
BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE (@DeptId IS NULL OR DeptId=@DeptId)
 AND (@OfficeId IS NULL OR OfficeId=@OfficeId)
END

Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.

I hope it will be helpful to those who are looking a solution for the above said problem. Please share your thoughts and ideas regarding this post.

Thank you.

About these ads