IF EXISTS in SWITCH statement

Question

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.

"doesn't work" is too vague. What values do you have in the tables and variables, What are you expecting to happen and what value is currently being returned. — Phil Blackburn, Jul 21 '16 at 09:01
The error I get is "Incorrect syntax near IF" (line 6). Here's the logic of the query: I have different variables (as in categories) and I want to check some bit fields for each one. If all of the bit fields are true I want the sproc to return true, else the sproc will return false. Based on the input I have to check one @var through switch clause. — Chriz, Jul 21 '16 at 09:12

score 1 · Accepted Answer · edited May 23 '17 at 11:44

A CASE WHEN construct requires the THEN part to be followed by an expression. You cannot put statements there, such as IF or RETURN.

Instead you should place an expression there, so that when the SELECT statement has evaluated it, and has produced its results, you can use that value to perform further statements with it, including IF or RETURN.

So you could replace this:

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

with a RETURN statement that gets the returned value from the SELECT, also using Count and Sign functions to produce the 0 or 1 value:

RETURN SELECT CASE @var 
        WHEN 1 THEN
           (SELECT     Sign(Count(*)) 
            FROM       Table2 
            INNER JOIN Table1 
                    ON Table2.IDNO=Table1.IDNO 
            WHERE      [Check1]=1 and [Check2]=1 and [Check3]=1 and Table1.IDNO=@IDNO)

IF EXISTS in SWITCH statement

1 Answers1