IF EXISTS in a SWITCH clause

Transact-SQL
1

Hello,

I have the following code that doesn't work. What I want to do is based on some variable I have to check if all bit fields of that variable are true. If yes, return true else return false.

Any suggestions?

DECLARE @var NVARCHAR(20);

IF EXISTS (SELECT variable FROM Table1  WHERE IDNO=@IDNO)
BEGIN
   SELECT @var=variable FROM Table1 WHERE IDNO=@IDNO
   SELECT CASE @var 
     WHEN 1 THEN
	  IF EXISTS(SELECT CheckFlag FROM Table2  INNER JOIN Table1 ON Table2.IDNO=Table1.IDNO WHERE [Check1]=1 and [Check2]=1 and [Check3]=1 and Table1.IDNO=@IDNO)
	   RETURN 1
	  ELSE 
	   RETURN 0
	 WHEN 2 THEN
	  ......
    ELSE -1
END

Thank you in advance.

2

CASE is an expression, it doesn't work like IF and the two are not interchangeable.
In your example, you should be nesting another CASE:

SELECT CASE @var
        WHEN 1
            THEN CASE 
                    WHEN EXISTS (
                            SELECT CheckFlag
                            FROM Table2
                            INNER JOIN Table1 
                                ON Table2.IDNO = Table1.IDNO
                            WHERE [Check1] = 1
                                AND [Check2] = 1
                                AND [Check3] = 1
                                AND Table1.IDNO = @IDNO
                            )
                        THEN 1
                    ELSE 0
                END
        WHEN 2
            THEN ...
        ELSE - 1
    END
1 Like
3

Thank you that works :slight_smile: