Sql Case statement within Sql IN

I have a sql statement which have a where condition like below

WHERE (TABLE1.Field=@Id OR (@Id=0))

This works fine when @Id values is zero or more

Now I need to match multiple values when @Id value become 0.

I tried something like below which doesn't work. I know that I can use IF-ELSE in here, but wonder whether this can be achieve using case statemet

WHERE TABLE1.Field IN (CASE WHEN @Id>0 THEN @Id ELSE (6,16,18) END)

回答1:

You can do it using an OR:

WHERE   (@Id > 0 AND Table1.Field = @Id)
OR      (@Id = 0 AND Table1.Field IN (6,16,18))

However, I would advise using (as you have said) IF/ELSE, when mashing together two conditions like this you can often force suboptimal plans. e.g In your example you could simplify this to a schema as follows:

CREATE TABLE T
(   ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
    Field INT NOT NULL, 
    SomeOtherField INT NULL
);
GO
INSERT T  (Field)
SELECT  Number
FROM    Master..spt_values
        CROSS JOIN (VALUES (1), (2), (3)) t (A)
WHERE   Type = 'P'
GO
CREATE NONCLUSTERED INDEX IX_T_Field ON T (Field) INCLUDE (SomeOtherField);

This simply populates one of the columns with the numbers 0-2047 repeated 4 times each (just for some example data). Then If I create two procedures, one that uses 'IF/ELSE' one that combines the criteria as above:

CREATE PROCEDURE dbo.Test @ID INT
AS
    SELECT  ID, Field, SomeOtherField
    FROM    T
    WHERE   (@Id > 0 AND T.Field = @Id)
    OR      (@Id = 0 AND T.Field IN (6,16,18))

GO
CREATE PROCEDURE dbo.Test2 @ID INT
AS
    IF @ID = 0
        SELECT  ID, Field, SomeOtherField
        FROM    T
        WHERE   T.Field IN (6, 16, 18)
    ELSE
        SELECT  ID, Field, SomeOtherField
        FROM    T
        WHERE   T.Field = @Id
GO

Since compilation of queries will only happen once (unless you explicitly say otherwise), the optimiser will not pick a different plan depending on whether you pass 0 or pass an ID > 0 to the procedure, so both of the following:

EXECUTE dbo.Test 0;
EXECUTE dbo.Test 1;

Will give this plan:

The second procedure is able to estimate the best execution plan much better so running this:

EXECUTE dbo.Test2 0;
EXECUTE dbo.Test2 1;

Gives the following plan:

Real world examples will obviously vary, and I have deliberately constructed an example that proves my point. It is slightly more effort to duplicate a lot of code by using IF/ELSE, but it is often worth it.

回答2:

WHERE TABLE1.Field IN (case when @Id>0 THEN @Id else 6 end,
            case when @Id>0 THEN @Id else 16 end,
            case when @Id>0 THEN @Id else 18 end)