SQL Server Case Statement Inside Where Clause Generates Error-CodePudding

Using the below SQL but getting error in case statement inside where clause.

create procedure MyProcedure 
    @ApprovalFilter as nvarchar(50), 
    @BothPendingAndApproved as nvarchar(50)

Select 
    * 
from 
    myTable
Where
    data = "A"
    and 
        case @BothPendingAndApproved 
            when 'Both' then ApprovalStatus <> 'Rejected'
            when 'Specific' then ApprovalStatus like @ApprovalFilter
        end
    and 
    Data2="B"

End

Why this part is failing?

    case @BothPendingAndApproved 
        when 'Both' then ApprovalStatus <> 'Rejected'
        when 'Specific' then ApprovalStatus like @ApprovalFilter
    end

CodePudding user response：

CASE is not a statement but an expression, which returns a scalar value. You could refactor the CASE expression for use as a predicate:

Select 
    * 
from 
    myTable
Where
    data = 'A'
    and 
        case  
            when @BothPendingAndApproved = 'Both' and ApprovalStatus <> 'Rejected' THEN 1
            when @BothPendingAndApproved = 'Specific' and ApprovalStatus like @ApprovalFilter THEN 1
        end = 1
    and 
    Data2='B'

IMHO, it's a bit clearer to use an OR condition:

create procedure MyProcedure 
    @ApprovalFilter as nvarchar(50), 
    @BothPendingAndApproved as nvarchar(50)
AS
Select 
    * 
from 
    myTable
Where
    data = 'A'
    and (
            (@BothPendingAndApproved = 'Both' and ApprovalStatus <> 'Rejected')
            OR (@BothPendingAndApproved = 'Specific' and ApprovalStatus like @ApprovalFilter)
        )
    and 
    Data2='B'
    OPTION(RECOMPILE); --consider this option to optimize the query

Note the use of single quotes for character literals for ANSI compatibility and use of SQL Server features like filtered indexes, indexed views, etc.

CodePudding user response：

As explained in the comments CASE is just not working like that, you can use "simple" AND/OR conditions:

Where
   data = "A"
   and 
   (
     (@BothPendingAndApproved  = 'Both' AND ApprovalStatus <> 'Rejected')
     OR 
     (@BothPendingAndApproved = 'Specific' AND ApprovalStatus like @ApprovalFilter)
   )
   and 
   Data2="B"