Can we pass null to sql parameter to query all?

Question

I have a query as follows

select * from table where col1 = @param1 and col2 = @parm2

And another

select * from table where col1 = @param1

Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them.

My queries are very big and i have to create 2 versions of sp's for each and i was thinking that can i try to avoid creating two.

Answer 1

Well, you can try this, but I don't think it will be very performant:

SELECT * FROM tab WHERE col1 = @param1 AND col2 = ISNULL(@parm2, col2)

Answer 2

SELECT * from table where col1 = @param1 and col2 = isnull(@parm2, col2)

ought to do what you're looking for.

Answer 3

You could try something like:

    select * from table where coalesce(@param1, col1) = col1 
and coalesce(@param2, col2) = col2

Answer 4

How about this:

select *
  from table
  where where (col1 = @param1 and col2 = @parm2)
  or (col1 = @param1 and parm2 is null)

Answer 5

If you use Stored procedures!

IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ] 

In your scenario. something like

if (@param1 = null)
Begin
 select * from table where col2 = @parm2
( 
End

else if (@param1 = 'something' )
Begin
(
 select * from table where col1 = @param1
End

reference : http://msdn.microsoft.com/en-us/library/ms182717.aspx

Answer 6

All the suggestions here about using COALESCE or ISNULL will work - effectively doing this:

select *
from table
where (@param1 IS NULL OR col1 = @param1)
    and (@parm2 IS NULL OR col2 = @parm2)

But you may need to watch out for parameter sniffing. SQL Server 2005 does not have the OPTIMIZE FOR UNKNOWN - you can mask the parameters into local variables in the SP to help avoid that or use RECOMPILE option.