SQL Server 2008 - ignoring null values

Question

I've got the following query (run in an sproc):

DECLARE @BrandId uniqueidentifier

SELECT * FROM Products p WHERE p.BrandId = @BrandId

My question is - when NULL is passed into @BrandId the query (correctly) returns no results - is there a way of telling SQL to return all rows when NULL is passed into @BrandId (without using lots of IF statements i.e. - IF @BrandId IS NULL etc)? - i.e. if @BrandId = NULL when 'ignore' that part of the where clause.

The above is a simplified example - the real query is longer and has multiple variables (like @BrandId) that can be passed null values - and the desired behaviour is for them not to limit the query results when a null value is passed.

Thanks!

Dave Markle · Accepted Answer · 2009-09-20 10:36:39Z

2

DECLARE @BrandId uniqueidentifier;

SELECT * FROM Products p WHERE p.BrandId = ISNULL(@BrandId, p.BrandId);

My only comment here is that if you have a LOT of products, this query pattern will not be highly optimized for the case when BrandId is NULL (if you put the query in a stored procedure) because only one plan can be stored per procedure per statement.

If you are using this statement in a stored proc and you really care about high performance (that is, if you have a lot of products and you are running this query a lot), you should use:

IF @BrandId IS NULL BEGIN
    SELECT * FROM Products p;
END ELSE BEGIN
    SELECT * FROM Products p WHERE p.BrandId = @BrandId;
END

edited Sep 20, 2009 at 10:36

answered Sep 20, 2009 at 9:55

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GilaMonster Over a year ago

That form is also prone to parameter sniffing, can result in very poor exec plans, depending how the proc is called first time. sqlinthewild.co.za/index.php/2009/09/15/…

Collectives™ on Stack Overflow

SQL Server 2008 - ignoring null values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related