Using a CASE with the IN clause in T-SQL

问题:

In My WHERE Clause I am using a CASE that will return all rows if parameter is blank or null. This works fine with single valuses. However not so well when using the IN clause. For example:

This works very well, if there is not match then ALL records are returned!

AND 
Name = CASE WHEN @Name_ != '' THEN @Name_ ELSE Name END
AND

This works also, but there is no way to use the CASE expression, so if i do not provide some value i will not see any records:

AND
Name IN (Select Names FROM Person Where Names like @Name_)
AND 

Can I use a CASE with the 2nd example? So if there is not a match all Names will be returned?

回答1:

Maybe coalesce will resolve your problem

AND
Name IN (Select Names FROM Person Where Names like coalesce(@Name,Name))
AND 

As Mattfew Lake said and used, you can also use isnull function

Name IN (Select Names FROM Person Where Names like isnull(@Name,Name)) 

回答2:

No need for a CASE statement, just use a nested OR condition.

AND (   Name IN (Select Names FROM Person Where Names like @Name_)
    OR
        @Name_ IS NULL 
    )
AND

回答3:

Perhaps something like this?

AND
Name IN (Select Names FROM Person Where Names LIKE 
         CASE WHEN @Name_ = '' OR @Name_ IS NULL THEN '%' ELSE @Name_ END)
AND 

This will use the pattern '%' (which will match everything) only when a null or blank @Name_ parameter is provided, otherwise it will use the pattern specified by @Name_.

Alternatively, something like this should work:

AND
Name IN (Select Names FROM Person Where Names LIKE 
         ISNULL( NULLIF( @Name_, '' ), '%' ))
AND 

回答4:

This works

DECLARE @Name NVARCHAR(100)

SET @Name = ''

DECLARE @Person TABLE ( NAME NVARCHAR(100) )

INSERT  INTO @Person VALUES  ( 'fred' )
INSERT  INTO @Person VALUES  ( 'jo' )

DECLARE @Temp TABLE
    (
      id INT ,
      NAME NVARCHAR(100)
    )

INSERT  INTO @Temp ( id, NAME ) VALUES  ( 1, N'' )
INSERT  INTO @Temp ( id, NAME ) VALUES  ( 5, N'jo' )
INSERT  INTO @Temp ( id, NAME ) VALUES  ( 2, N'fred' )
INSERT  INTO @Temp ( id, NAME ) VALUES  ( 3, N'bob' )
INSERT  INTO @Temp ( id, NAME ) VALUES  ( 4, N'' )

SELECT  * FROM    @Temp
WHERE   name IN ( SELECT    name
                  FROM      @Person
                  WHERE     name = CASE WHEN @name != '' THEN @Name
                                        ELSE name
                                   END )

回答5:

You should almost definitely use an IF statement with two selects. e.g.

IF @Name IS NULL
    BEGIN
        SELECT  *
        FROM    Person
    END
ELSE
    BEGIN
        SELECT  *
        FROM    Person
        --WHERE Name LIKE '%' + @Name + '%'
        WHERE   Name = @Name
    END

N.B. I've changed like to equals since LIKE without wildcards it is no different to equals, , it shouldn't make any difference in terms of performance, but it stops ambiguity for the next person that will read your query. If you do want non exact matches then use the commented out WHERE and remove wildcards as required.

The reason for the IF is that the two queries may have very different execution plans, but by combining them into one query you are forcing the optimiser to pick one plan or the other. Imagine this schema:

CREATE TABLE Person
(       PersonID        INT IDENTITY(1, 1) NOT NULL,
        Name            VARCHAR(255) NOT NULL,
        DateOfBirth     DATE NULL
    CONSTRAINT PK_Person_PersonID PRIMARY KEY (PersonID)
);
GO
CREATE NONCLUSTERED INDEX IX_Person_Name ON Person (Name) INCLUDE (DateOfBirth);
GO
INSERT Person (Name)
SELECT  DISTINCT LEFT(Name, 50)
FROM    sys.all_objects;
GO

CREATE PROCEDURE GetPeopleByName1 @Name VARCHAR(50)
AS
    SELECT  PersonID, Name, DateOfBirth
    FROM    Person
    WHERE   Name IN (SELECT Name FROM Person WHERE Name LIKE ISNULL(@Name, Name));
GO

CREATE PROCEDURE GetPeopleByName2 @Name VARCHAR(50)
AS
    IF @Name IS NULL
        SELECT  PersonID, Name, DateOfBirth
        FROM    Person
    ELSE
        SELECT  PersonID, Name, DateOfBirth
        FROM    Person
        WHERE   Name = @Name;
GO

Now If I run the both procedures both with a value and without:

EXECUTE GetPeopleByName1 'asymmetric_keys';
EXECUTE GetPeopleByName1 NULL;

EXECUTE GetPeopleByName2 'asymmetric_keys';
EXECUTE GetPeopleByName2 NULL;

The results are the same for both procedures, however, I get the same plan each time for the first procedure, but two different plans for the second, both of which are much more efficient that the first.

If you can't use an IF (e.g if you are using an inline table valued function) then you can get a similar result by using UNION ALL:

SELECT  PersonID, Name, DateOfBirth
FROM    Person
WHERE   @Name IS NULL
UNION ALL
SELECT  PersonID, Name, DateOfBirth
FROM    Person
WHERE   Name = @Name;

This is not as efficient as using IF, but still more efficient than your first query. The bottom line is that less is not always more, yes using IF is more verbose and may look like it is doing more work, but it is in fact doing a lot less work, and can be much more efficient.