Chriz Chriz - 4 months ago 11
SQL Question

IF EXISTS in SWITCH statement

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.

Answer

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)