asked on

can we exit out of case statement in sql server 2005

Hi,
Could you please tell me if we can exit out of a case statement when one of the conditions satisfy?

Thanks!

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

The CASE statement is automatically exited when a condition is met. No further processing of the statement is done. If all WHEN conditions are parsed and none are TRUE, the ELSE will be executed, if present.

SOLUTION

zackis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipjyo

ASKER

I have comparison statements as follows
case
when isnull(@val1,'') = isnull(@val2,'') then @flag=1 else @flag=0
when isnull(@val3,'') = isnull(@val4,'') then @flag=1 else @flag=0
when isnull(@val5,'') = isnull(@val6,'') then @flag=1 else @flag=0
end

I want the case statement to be exit when @flag=0
Please suggest me
Thanks!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zackis

Is this in a Select statement??? How is that being used?

SOLUTION

jport88

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipjyo

ASKER

This is not a select statement
actually I have to check the below condition for 80 columns and on first occurence it should come out of those 80 statements with @flag = 0 and should not execute the remaining statements.
when isnull(@val3,'') <> isnull(@val4,'') then 0
Now I have another if condition as follows
if @flag=0
so Basically the SP is looking for a @flag=0 to process some other statements.
Please suggest me if you also have any other alternatives to implement this.
By the way, will the case statement exit if one of the conditions satisfies?

Thanks a lot!

Guy Hengel [angelIII / a3]

>By the way, will the case statement exit if one of the conditions satisfies?
yes.

see with my suggestion of how to make yuur code to be changed so that on the first condition to "set" the value to 0, it will "exst".

ipjyo

ASKER

jport88,

Based on your explanation I guess I should have the following as angelll also suggested. so I believe it will exit on the first occurance of the condition @flag = 0 and does not process the remaining conditions.

set @flag = case when isnull(@val1,'') <> isnull(@val2,'') then 0
when isnull(@val3,'') <> isnull(@val4,'') then 0
when isnull(@val5,'') <> isnull(@val6,'') then 0
else 1
end

Thanks!

ipjyo

ASKER

ok angellll.
Thank you all!
I will let you know once I try this

SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

You will want to use <> as AngelIII suggests and change all 0 to 1 and 1 to 0.

Guy Hengel [angelIII / a3]

@dbbishop: are you sure :=)

ipjyo

ASKER

Thank you so much for all the help you provided

D B

Angel: Yeah, if he wants to quit processing and set the indicator to 0 as soon as he gets condition where a pairing is not equal. In his initial statement he had the example:

when isnull(@val1,'') = isnull(@val2,'') then @flag=1 else @flag=0 ...

with the comment "I want the case statement to be exit when @flag=0 "