Is there a way to do a conditional Select?

Question

question

PeterH asked Oct 28, '09

Is there a way to do a conditional Select?

Let's say I have the following:

SELECT RecordID from Products where RecordType = @RecordType and RecordName = @RecordName.

Let's say sometimes a RecordType is specified and sometimes it isn't. So, if @RecordType is null, I don't want that in the where. So, I could do:

If @RecordType is Null                     
   SELECT RecordID from Products where RecordName = @RecordName                    
Else                    
   SELECT RecordID from Products where RecordType = @RecordType and RecordName = @RecordName

But is there any more elgant way to do this within the original SELECT so that the IF isn't required? I have a lot of these and would prefer not to have to have multiple versions of every select or duplicate procedures.

Note, my RecordType field is never null in the database, but it is not always desired as a criteria for the SELECT

select

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-28T17:31:49Z

TG answered Oct 28, '09

Here are a couple options:

SELECT RecordID             
from   Products             
where  RecordType = coalesce(@RecordType, RecordType)            
and    RecordName = coalesce(@RecordName, RecordName)            
            
or            
            
SELECT RecordID             
from   Products             
where  (@RecordType is null OR RecordType = @RecordType)            
and    (@RecordName is null OR RecordName = @RecordName)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question