0

I have a situation where if an input variable is equal to a certain string, I want to evaluate a condition, and if it's not, I don't care about that condition.

I've tried to do it with both IF and CASE but get incorrect syntax errors. Maybe this isn't possible or maybe I just don't have all the parentheses in the right places?

    Declare @state nvarchar(255) = 'KY'
,@group nvarchar(255) = 'COM'

SELECT * from business_auth
where (case when @group = 'COM'
            then (@state = AddressState or AddressState is null)
            else 1=1
            end)

I want it to look at the state column if the group is COM but for any other group, I don't care about the state. I have a long list of other AND evaluations as well here, so I'd prefer not to have to re-write the whole thing or put the IF before the query, and have two versions of the query in the procedure, one evaluating state and one not doing so. It seems like there would be a more eloquent solution, but if there's not, I'll go with that.

Emily Anderson
  • 11
  • 1
  • 1
    `CASE` is an **expression**; it returns a scalar value, not a boolean result. – Thom A Nov 08 '19 at 17:12
  • [This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer applies to using `case` in `where` and `on` clauses. For your purpose simple boolean logic is probably easier, e.g. `where ( @group != 'COM' ) or ( @state = AddressState or AddressState is null )` unless `@group` can be null. – HABO Nov 08 '19 at 17:43

2 Answers2

0

This seems like you would be better off with dynamic SQL:

DECLARE @state nvarchar(255) = N'KY',
        @group nvarchar(255) = N'COM';


DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT *' + @CRLF +
           N' FROM dbo.business_auth' +
           CASE WHEN @group = 'COM' THEN @CRLF + N'WHERE @state = AddressState OR AddressState IS NULL' ELSE N'' END + N';';

EXEC sp_executesql @SQL, N'@state nvarchar(255)', @State;
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

You can do this without dynamical SQL:


SELECT * from business_auth
where (@group = 'COM' AND (@state = AddressState or AddressState is null))
      OR NOT (@group = 'COM')
Roma Ruzich
  • 692
  • 5
  • 18