SQL case statement in where clause fails-CodePudding

This works fine

CREATE PROCEDURE [dbo].[MyProcedure] 
    @Cond_1 AS nvarchar(50), 
    @Cond_2 AS nvarchar(50),
    @ApprovalFilter AS nvarchar(1)
AS 
BEGIN
    SELECT * 
    FROM MyTable
    WHERE Field_1 = @Cond_1 
      AND Field_2 = @Cond_2
END

This fails

CREATE PROCEDURE [dbo].[MyProcedure] 
    @Cond_1 AS nvarchar(50), 
    @Cond_2 AS nvarchar(50),
    @ApprovalFilter AS nvarchar(1)
AS 
BEGIN
    SELECT *   
    FROM MyTable
    WHERE
        CASE
            WHEN @ApprovalFilter = 'A' 
                THEN [My Approval Column] IS NULL
            WHEN @ApprovalFilter = 'B' 
                THEN [My Approval Column] IS NOT NULL
            ELSE [My Approval Column] LIKE '%'
        END;
        AND Field_1 = @Cond_1 
        AND Field_2 = @Cond_2
END

Specifically this part is causing the problem

    CASE
        WHEN @ApprovalFilter = 'A' 
            THEN [My Approval Column] IS NULL
        WHEN @ApprovalFilter = 'B' 
            THEN [My Approval Column] IS NOT NULL
        ELSE [My Approval Column] LIKE '%'
    END;

Any suggestion about how to handle this?

Using Microsoft SQL Server

CodePudding user response：

The result of your CASE expression is a boolean. E.g. [My Approval Column] IS NULL is either true or false. SQL Server, however, does not support a boolean data type, so it results in an error.

As the WHERE clause alreeady contains a boolean expression, you don't have to use CASE expressions there, anyway. Just use AND and OR with the appropriate parentheses.

I suppose you want something along the lines of

SELECT *
FROM MyTable
WHERE 
(
   (@ApprovalFilter = 'A' AND [My Approval Column] IS NULL)
  OR
   (@ApprovalFilter = 'B' AND [My Approval Column] IS NOT NULL)
  OR
   @ApprovalFilter NOT IN ('A', 'B')
  OR
   @ApprovalFilter IS NULL)
)
AND Field_1 = @Cond_1 
AND Field_2 = @Cond_2

CodePudding user response：

First of all, the usage of CASE is not correct - it has to be on one side of an equation. However, I would suggest something like this:

WHERE ([My Approval Column] IS NULL AND 1 = CASE(@ApprovalFilter)
                                              WHEN 'A' THEN 1
                                              ELSE 0
                                            END)
  OR ([My Approval Column] IS NOT NULL AND 1 = CASE(@ApprovalFilter)
                                                 WHEN 'B' THEN 1
                                                 ELSE 0
                                               END)
  OR @ApprovalFilter NOT IN ('A', 'B')