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.

12 thoughts on “SQL Server: An optimal way to create procedure with multiple optional parameters

  1. I was able to use your solution in a query for my Visual Studio project. I had been looking for almost 3 weeks for this, so many thanks!

  2. Hi, is there some solution like this for DB2? There is no errors with compiler but if some parameter is null, result will be empty.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s