Select SQL records with blank values

I have a stored proc used in SSRS reports which has to return records with blank values for confirmation number field if a blank value is passed or a record with the specified confirmation number if a confirmati开发者_开发技巧on number is passed in. It has to return all records if a null value is passed in.

My sql right now is

(IsNull(@ConfirmationNumber,'') = '' or RoomRegistration.ConfirmationNumber 
like '%' + @ConfirmationNumber + '%')

my above sql query is returning all results when a blank or null value is passed. How do I modify it to return only the records with blank confirmation number when a blank value is passed and return all values when a null value is passed?

I even tried this variation

(ConfirmationNumber LIKE CASE WHEN @ConfirmationNumber ='' 
 THEN @ConfirmationNumber WHEN @ConfirmationNumber is NULL THEN '%%'
 ELSE '%' + @ConfirmationNumber + '%' END ) 

still doesnt work the way I wanted..Please advise

you where clause should look like so:

@ConfirmationNumber is null 
            OR RoomRegistration.ConfirmationNumber like '%' + @ConfirmationNumber + '%' ) 
            OR (IsNull(RoomRegistration.ConfirmationNumber, '') = '' AND @ConfirmationNumber = '')

Been awhile though :)

Try this

( ConfirmationNumber LIKE CASE WHEN @ConfirmationNumber ='' THEN @ConfirmationNumber WHEN @ConfirmationNumber is NULL THEN ConfirmationNumber ELSE '%' + @ConfirmationNumber + '%' END )

When the parameter is null the expression (ConfirmationNumber = ConfirmationNumber) will evaluate which is always true .