question

PeterH avatar image
PeterH asked

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 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
TG avatar image
TG answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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