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.

CodeProject