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.

possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — Conrad Frix, Mar 04 '14 at 18:38

score 31 · Answer 1 · answered Mar 04 '14 at 18:33

31

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)

answered Mar 04 '14 at 18:33

Shantanu Gupta

20,688
54
182
286

1

You sir are a goddam genius. – Danrex Aug 06 '17 at 23:49
Don't forget the parentheses, otherwise it won't work. – Pablo Carrasco Hernández Apr 22 '19 at 20:33
What if I'm trying to use the LIKE operator instead of equals? Would it be LIKE '%@Name%' or %'@Name'% or some other way? – Catlover Sep 25 '19 at 13:54

M.Ali · Answer 2 · 2019-12-03T22:59:32.200

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;

Why is the second one better? Can you please elaborate a bit more? TY. — aderchox, Aug 11 '20 at 00:45
The second case provided omits any not included parameters. This way, the SQL saves time on not checking them altogether. — as.beaulieu, May 12 '21 at 16:49

score 3 · Answer 3 · answered Mar 04 '14 at 18:25

3

You can use a dynamic query

SELECT @sql=
'SELECT DISTINCT [Test_Id], [Test].[Test_Name], [Test].[Test_Date]
    FROM [Test]
    WHERE (1 = 1)'

IF @start IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] >= @start)'
IF @end IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] <= @end)'

SELECT @paramlist = '@start datetime, 
    @end datetime'

EXEC sp_executesql @sql, @paramlist,
    @start, 
    @end

You can also use different queries but if you have more than 2 parameters it's very annoying code

IF(@start IS NULL)
    IF(@end IS NULL)
        SELECT ...
    ELSE
        SELECT ...
ELSE
    IF(@end IS NULL)
        SELECT ...
    ELSE
         SELECT ...

answered Mar 04 '14 at 18:25

meziantou

20,589
7
64
83

Thanks for your prompt suggestion..actually your main select statement is using only one table i.e 'Test', but in my case I have to select values from 4-5 different tables using joins etc..so I think dynamic sql might be a bit tedious :). anyway I am trying on it.. Thanks – vicky Mar 04 '14 at 18:31
Why can't you use JOIN with dynamic queries: `FROM Test JOIN ...` – meziantou Mar 04 '14 at 18:35
I know this question is labled with sql-server but the sp_executesql doesn't work in mysql. – aderchox Aug 11 '20 at 00:49

sql search query with multiple optional search parameters

3 Answers3

Linked