CODEWITHSUNDEEP
sqlsql-server
user9758771
user9758771

Reputation: 139

SQL Server Where clause with Case Checking null value

So basically performing a name search on first and last name. The first name search parameter is not required, so I need a case statement in the where clause. However, the field first name in the table I'm searching can also be null.

And the problem is in SQL Server to check for a null you must use is null or is not null and I can't seem to use that with a case. The regex % doesn't cover null values.

Is this possible in one query or will this need to be two separate queries?

Here is essentially what it looks like:

select *
from table
where first_name like case
                       when @pFirstName is not null then @pFirstName
                       when @pFirstName is null then '%' end
and last_name = @pLastName

Upvotes: 0

Views: 2558

Answers (6)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use Boolean logic :

WHERE ( (@pFirstName IS NOT NULL AND first_name = @pFirstName) OR
        (@pFirstName IS NULL)
      ) AND (last_name = @pLastName);

However, the first logic you can evaluate also :

(@pFirstName IS NULL OR first_name = @pFirstName)

By using that your query would be SARGable.

Upvotes: 4

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You could use a ISNULL() in order to handle passing of one argument or not.

I don't really recommend using this in a query, but you can do something like:

select *
from table
where first_name = ISNULL(@pFirstName, first_name)
    and last_name = @pLastName;

However, a better version would be:

select         /* do you really need all (*) the columns? */
    column_1
    , column_2
    , column_3 
from table
where first_name = ISNULL(@pFirstName, first_name)
    and last_name = @pLastName
OPTION (RECOMPILE);

I'm adding OPTION (RECOMPILE) so that your query performs optimally (as far as it can in this form), by recalculating row estimates in case the @pFirstName parameter is not provided.

Ideally you would treat passing a parameter with a IF:

IF @pFirstName IS NOT NULL
    BEGIN
        select
            column_1
            , column_2
            , column_3 
        from table
        where first_name = @pFirstName
            and last_name = @pLastName;
    END
ELSE
    BEGIN
        select
            column_1
            , column_2
            , column_3 
        from table
        where last_name = @pLastName;
     END

Upvotes: 3

GuidoG
GuidoG

Reputation: 12059

Something like this can do it

select *
from   table
where  (first_name is null or first_name = @pFirstName)
and    last_name = @pLastName

if you need a like it will look more like this

select *
from   table
where  (first_name is null or first_name like '%' + @pFirstName + '%')
and    last_name like '%' + @pLastName + '%'

Upvotes: 3

Cato
Cato

Reputation: 3701

...WHERE  (first_name like @pFirstName OR @pFirstName IS NULL) 
            AND last_name = @pLastName

Upvotes: 1

Slava Murygin
Slava Murygin

Reputation: 1955

I think you need this one:

select * from table where last_name = @pLastName and 
    (@pFirstName is null or first_name = @pFirstName)

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this:

select *
from table
where (@pFirstName is null or first_name = @pFirstName)
  and last_name = @pLastName;

If @pFirstName is null, then (@pFirstName is null or first_name = @pFirstName) evaluates to true. If @pFirstName is not null, then the whole expression will be true whenever first_name = @pFirstName is true.

Upvotes: 3

Related Questions