SQL Server Case Statement Query-CodePudding

I am writing a stored procedure which has some parameters and want to write a case statement in query giving me syntax error. Can you help me how to write case statement?

Select *
FROM table1 ssr
left join table2 sse on ssr.case_nbr=sse.case_nbr
left join table3 m on m.MemberId=sse.memberid
where 
    (
        @searchValue IS NULL Or
        concat(last_name, first_name) like '%'   @searchValue   '%' 
        or mrn like '%'   @searchValue   '%' or FIN like '%'   @searchValue   '%' 
    )
AND (@searchFirst IS NULL Or first_name like '%'   @searchFirst   '%')

//i want to write a case statement 
AND (@statusfilter IS NULL OR 
                    CASE 
                    WHEN CHARINDEX('NEW', @statusfilter) = 0
                    THEN
                        (sse.Status in (select splitdata from dbo.fnSplitString(@statusfilter,'|')))
                    ELSE
                        (sse.Status is Null OR sse.Status in (select splitdata from dbo.fnSplitString(@statusfilter,'|')))
                        
                    END
    )

CodePudding user response：

You have to return value from the CASE, You can use IIF inside THEN and return 1 or 0 and compare that value.

    AND (
        @statusfilter IS NULL 
        OR 
            1 = CASE 
                    WHEN CHARINDEX('NEW', @statusfilter) = 0 
                        THEN IIF(sse.Status in (select splitdata from dbo.fnSplitString(@statusfilter,'|')), 1, 0) 
                    ELSE IIF((sse.Status is Null OR sse.Status in select splitdata from dbo.fnSplitString(@statusfilter,'|')), 1, 0)
                END
    )

CodePudding user response：

You can just use normal boolean logic, which after cleaning up becomes this:

AND (
      @statusfilter IS NULL OR 
      (
        (
          (CHARINDEX('NEW', @statusfilter) > 0 AND sse.Status IS NULL)
        )
        OR sse.Status in (select splitdata from dbo.fnSplitString(@statusfilter, '|'))
      )
    )

Note that I strongly recommend you use a Table Valued Parameter instead of a split string.

At the very least, you should use the built-in string splitting function STRING_SPLIT (and I hope you are not on an unsupported version which doesn;t have it).