sql search query with multiple optional search parameters

Question

I have a query in sql 2008 where a use can select multiple optional parameters like these:

@DateFrom
@DateTo
@UserType
@Customer
@User

What's the best / most performant approach to filtering based on all of these options?

• separate select statements for different cases
• using coalesce
• etc.

Answer 1

You can avoid using multiple IF ELSE condition and dynamic query by using this approach.

SELECT * FROM TBL
WHERE (@Name IS NULL OR Name = @Name) 
AND (@Age IS NULL OR Age = @Age)

Answer 2

you can write you WHERE Clause something like this.....

This is a quick fix but a bad approach,,,,,

SELECT ......
FROM TABLE
WHERE 
     (@UserType IS NULL OR UserType = @UserType)
AND
    (@Customer IS NULL OR Customer = @Customer)
AND
    (@User IS NULL OR User = @User)
AND
    (<Similarly Other Conditions>)

The proper way of writing a query like this should be using dynamic SQL with the use of sp_executesql

-- Optional Variables

Declare @UserType   VARCHAR(10) = NULL
    ,   @Customer   INT = 123
    ,   @User       INT = 123
    ,   @Sql        NVARCHAR(MAX);


-- Build SQL Dynamically 

 SET @Sql = N'  SELECT *
                FROM TABLE_Name
                WHERE 1 = 1 '
          + CASE WHEN @UserType IS NOT NULL THEN
            N' AND UserType = @UserType ' ELSE N' ' END
          +  CASE WHEN @Customer IS NOT NULL THEN
            N' AND Customer = @Customer ' ELSE N' ' END
          +  CASE WHEN @User IS NOT NULL THEN
            N' AND User = @User ' ELSE N' ' END

-- Finally Execute SQL 

 Exec sp_executesql @Sql
                , N'@UserType VARCHAR(10) , @Customer INT , @User INT'
                , @UserType
                , @Customer
                , @User;