stored proc return all rows when parameters are null

Question

when the optional parameters are null or empty how can i ignore that condition? e.g.

declare @color nvarchar(50)
declare @time datetime

set @color = null

select * 
  from apples 
 where apple.color = @color 
   and apple.time = @time

if @color or @time is null , i would like to ignore that condition.

Answer 1

Syntax depends on your exact RDBMS, but for MySQL:

SELECT * 
FROM   apples 
WHERE  apple.color = IFNULL( @color, apple.color )
AND    apple.time  = IFNULL( @time, apple.time )

and so on for other attributes.

Answer 2

Try this:

select * 
 from apples 
where 1 = Case 
           When @color is null then 1 
           else 
             Case 
               When apple.color = @color then 1 
               else 0 
             end 
           end 
  and 1 = Case 
            When @time is null then 1 
            else 
              Case 
                When apple.time = @time Then 1 
                else 0 
              end 
          end

Answer 3

SELECT *
FROM apples
WHERE (@color IS NULL OR apples.color = @color)
    AND (@time IS NULL OR apples.time = @time)

Answer 4

You can simply add "or @param is null" to each condition.

select * from apples where (apple.color = @color or @color is null) and (apple.time = @time or @time is null)

Another solution is to generate the SQL statement dynamically.

Answer 5

For the human reader, something like this seems to be preferred:

SELECT * 
  FROM apples 
 WHERE apple.color = COALESCE(@color, apple.color)
       AND apple.time = COALESCE(@time, apple.time);

and I understand that some optimizers handle this very well. Sadly, SQL Server's is not one of them: the above will result in a table scan, as will variations on the theme :(

Tony Rogerson SQL Server MVP did some good analysis on this and concluded:

The only way in SQL Server... to get an efficient, scalable and performant solution is to either use a ton of IF ELSE control of flow blocks (one per parameter combination) or use dynamic SQL.

So the answer rather depends on whether you are writing code that is either easy to understand, maintain and port to other SQL platforms in the future or performs well on one optimizer today.