Handling T-SQL Query with null filters as parameters

Question

Suppose I have a stored procedure where I'm passing in 5 filters as parameters. These parameters are filters to each column in my query. My query has 5 fields. What is the best way to prevent my query from throwing an exception/error if I have 1 or more NULL (or '') valued parameters for my filters?

    SELECT 
        [helpings_turkey],
        [helpings_green_beans],
        [scoops_squash],
        [qty_buscuits],
        [pieces_pie]
    FROM
        [thanksgiving_guest]
    WHERE 
        [helpings_turkey] > @helpings_turkey_filter
        AND [helpings_green_beans] > @helpings_green_beans_filter
        AND [scoops_squash] > @scoops_squash_filter
        AND [qty_buscuits] > @qty_buscuits_filter
        AND [pieces_pie] > @pieces_pie_filter

Dave Markle · Accepted Answer · 2012-09-21 15:03:58Z

Well, you won't get an error if the value is null, but you won't get results either.

You could do something like this:

[helpings_turkey] > ISNULL(@helpings_turkey_filter, X)

where "X" is something you know to be smaller than your smallest value.

This will work for small datasets, but likely won't be able to take advantage of any indexing you may have on your [thanksgiving_guest] table.

The most performant way of doing filters in patterns like this is to usually to assemble the SQL dynamically, by inserting clauses when non-null filter conditions are encountered like so:

SET @sql = 'SELECT 
        [helpings_turkey],
        [helpings_green_beans],
        [scoops_squash],
        [qty_buscuits],
        [pieces_pie]
    FROM
        [thanksgiving_guest]
    WHERE 1=1';

IF @helpings_turkey_filter IS NOT NULL BEGIN
    SET @sql = @sql + '[helpings_turkey] > @helpings_turkey_filter'
END
...
IF @pieces_pumpkin_pie_filter IS NOT NULL BEGIN
    SET @sql = @sql + '[pieces_pumpkin_pie] > @pieces_pumpkin_pie_filter'
END

exec sp_executesql @sql, N'@helpings_turkey_filter, ..., @pieces_pumpkin_pie_filter', @helpings_turkey_filter, @pieces_pumpkin_pie_filter`

Note that we still continue to parameterize the query even though we are assembling the query dynamically.

CandiedCode · Accepted Answer · 2012-09-21 14:59:25Z

1

 SELECT 
        [helpings_turkey],
        [helpings_green_beans],
        [scoops_squash],
        [qty_buscuits],
        [pieces_pie]
    FROM
        [thanksgiving_guest]
    WHERE 
        [helpings_turkey] > IsNull(@helpings_turkey_filter, 0)
        AND [helpings_green_beans] > IsNull(@helpings_green_beans_filter, 0)
        AND [scoops_squash] > IsNull(@scoops_squash_filter, 0)
        AND [qty_buscuits] > IsNull(@qty_buscuits_filter, 0)
        AND [pieces_pumpkin_pie] > IsNull(@pieces_pumpkin_pie_filter, 0)

Something like this. Use the parameter if its passed in, if not default to some value.I defaulted to zero.

answered Sep 21, 2012 at 14:59

CandiedCode

6041 gold badge5 silver badges7 bronze badges

Comments

paparazzo · Accepted Answer · 2012-09-21 15:25:49Z

1

Try this, as this will solve your problem (If you are passing more than one parameters which might be NULL:

SELECT 
        [helpings_turkey],
        [helpings_green_beans],
        [scoops_squash],
        [qty_buscuits],
        [pieces_pie]
    FROM
        [thanksgiving_guest]
    WHERE 
        [helpings_turkey] > ISNULL(@helpings_turkey_filter, 0)
        AND [helpings_green_beans] > ISNULL(@helpings_green_beans_filter, 0)
        AND [scoops_squash] > ISNULL(@scoops_squash_filter, 0)
        AND [qty_buscuits] > ISNULL(@qty_buscuits_filter, 0)
        AND [pieces_pumpkin_pie] > ISNULL(@pieces_pumpkin_pie_filter, 0)

edited Sep 21, 2012 at 15:25

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

answered Sep 21, 2012 at 15:09

Harshil Shukla

1372 silver badges14 bronze badges

Collectives™ on Stack Overflow

Handling T-SQL Query with null filters as parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related