SQL Server 2008 - ignoring null values

Question

Hi,

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!

Answer 1

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