How to use SQL IN operator with CASE in select query where clause-CodePudding

DECLARE  @AreaType NVARCHAR(250);
SET @AreaType = 'Test Area 1'; ---Test Area 2, Test Area 3, XYX

SELECT [Area_Category_Id] AreaCategoryId
              ,[Area_Category] AreaCategoryName
              ,[Is_Active] IsActive
          FROM [dbo].[LK_AreaCategories]
          --WHERE [Area_Category_Id] IN(1,2,3,4)   ------How do i put this in a case statement
          WHERE [Area_Category_Id] IN (CASE 
                        WHEN @AreaType = 'Test Area 1'
                            THEN 1
                        WHEN @AreaType = 'XYX' 
                            THEN 3
                        ELSE -1
                    END)

I have the above sample T-SQL code and in the IN operator of the where clause I want to use multiple values like IN (1,2,3,4) but also I want to use the CASE operator such that I can pass different values to the IN operator depending on the value of @AreaType parameter, how can I modify the code above to achieve that?

CodePudding user response：

You don't use a CASE expression for that (precisely because its an expression not a statement), you use regular AND/OR logic e.g.

WHERE (@AreaType = 'Test Area 1' AND Area_Category_Id in (1,2,3))
OR (@AreaType = 'Test Area 2' AND Area_Category_Id in (4,5,6))
OR (@AreaType = 'Test Area 3' AND Area_Category_Id in (7,8,9))