CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Russell Schutte
Russell Schutte

Reputation: 247

SQL: Conditional AND in where

I'm trying to create a stored procedure that allows parameters to be omitted, but ANDed if they are provided:

CREATE PROCEDURE 
    MyProcedure

    @LastName Varchar(30) = NULL,
    @FirstName Varchar(30) = NULL,
    @SSN INT = NULL
AS

SELECT LastName, FirstName, RIGHT(SSN,4) as SSN
FROM Employees
WHERE 
    (
        (LastName like '%' + ISNULL(@LastName, '') + '%')
            AND 
        (FirstName like '%' + ISNULL(@FirstName, '') + '%')
    )
AND
    (SSN = @SSN)

I only want to do the AND if there's an @SSN provided. Or is there some other way to do this?

If an SSN is provided, all records are returned by the LastName/FirstName part of the query. If just a lastname/firstname is provided, the AND makes it so no records are returned since the SSN won't validate.

Ideas?

Additional Clarification

Assume a basic recordset:

First          Last          SSN  
Mike           Smith         123456789  
Tom            Jones         987654321

IF we ALTER the Procedure above so it doesn't include this chunk:

AND
    (SSN = @SSN)

then everything works great, provided we're passed a FirstName or LastName. But I want to be able to include SSN, if one is provided.

If I change the AND to an OR, then I get a bad result set since the FirstName/LastName portion of the query evalute to:

WHERE (LastName like '%%' and FirstName like '%%')

(which will, of course, return all records)

If I can't conditionally abort the AND, I'm hoping for something like:

AND
    (SSN like ISNULL(@SSN, '%'))

:-)

Upvotes: 7

Views: 12062

Answers (5)

Data Masseur
Data Masseur

Reputation: 1193

CREATE PROCEDURE 
    MyProcedure

    @LastName Varchar(30) = NULL,
    @FirstName Varchar(30) = NULL,
    @SSN INT = -1
AS

SELECT LastName, FirstName, RIGHT(SSN,4) as SSN
FROM Employees
WHERE 
    (
        (LastName like '%' + ISNULL(@LastName, '') + '%')
            AND 
        (FirstName like '%' + ISNULL(@FirstName, '') + '%')
    )
AND
    (SSN = @SSN or @SSN = -1)

Just give @SSN a default value and you can use the AND all the time and worry about it.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

HERE's THE BIBLE on dynamic search parameters in SQL Server. You should write it like this

SELECT LastName, FirstName, RIGHT(SSN,4) as SSN
FROM Employees
WHERE (NULLIF(@LastName,'') IS NULL OR LastName LIKE '%' + @LastName + '%')
  AND (NULLIF(@FirstName,'') IS NULL OR FirstName LIKE '%' + @FirstName + '%')
  AND (@SSN is null or SSN = @SSN)
   -- or if not provided means blank, then
   -- (@SSN = '' or SSN = @SSN)
OPTION (RECOMPILE)

BTW, there's no such thing as a short circuit boolean in SQL Server. Yes it does stop at the first conclusion, but there's no guarantee the condition on the left is processed before the right. e.g. this code is NOT SAFE. I'm using 'ABC.123' but that could just as well be a column.

WHERE ISNUMERIC('ABC.123') = 1 OR CAST('ABC.123' AS INT) > 10

To illustrate on the AND (@SSN is null or SSN = @SSN) clause, for when @SSN is not provided, i.e. NULL

   AND (@SSN is null or SSN = NULL)
=> AND (NULL IS NULL or SSN = NULL)
=> AND (TRUE or SSN = NULL)
=> AND (TRUE)

-- In other words, this filter is "dissolved" and appears as if it never existed.
-- (in the scheme of a series of AND conditions)

Upvotes: 0

Bmo
Bmo

Reputation: 1212

You could put both versions of the statement in an if block.

 AS
 IF @SSN IS NULL
      /*regular statement*/
 ELSE
      /*Statement with @SSN*/

or

 AND
      SSN = ISNULL(@SSN, SSN)

I have never used the 2nd option but I know it exists.

Upvotes: 0

d89761
d89761

Reputation: 1434

You can also try:

(ISNULL(SSN, @SSN) = @SSN)

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

Change:

AND (SSN = @SSN)

to:

AND (SSN = @SSN or @SSN is null)

If SSN is never null, you could also do:

AND SSN = ISNULL(@SSN, SSN)

Upvotes: 17

Related Questions