Can I use Case?

Question

Can I use Case?

lindss01

SSCrazy

Points: 2264
More actions
June 1, 2005 at 11:38 am

#67091

I am trying to create a re-startable stored procedure. So I am accepting a parameter that will tell me where to start.
I'd like to use the following Case statement and expand it. But it would not let me.
CASE
WHEN @wheretostart = 'Leads' THEN GOTO LEADS
WHEN @wheretostart = 'AA' THEN GOTO APPENDEDATTRIBUTES
END
So I'm left with using this that works fine.
IF @wheretostart = 'Leads' GOTO LEADS
else IF @wheretostart = 'AA' GOTO APPENDEDATTRIBUTES

Now, in Books online it says the CASE statement can be used for Flow of Control statements. Can anybody shed some light on this? Why doesn't the CASE statement work?

Thanks
Steve

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I believe CASE assigns a value, while IF is flow control using equalities/in-equalities. That may be why that does not work.

(I'm sure you already know, GOTO can lead to some messy code...)

I wasn't born stupid - I had to study.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Case is like a function.

Can you please provide a link. My Books onlins says this.

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.
The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Regards,
gova

lindss01 SSCrazy Points: 2264 More actions · Answer 3

Control-of-Flow Language is the section that refers to the Case statement.

And yes I know that GOTO is messy but I don't think there is another way around it in this case. Basically, the procedure based on the parameter will skip parts of the procedure. So I'm always GO(TO)ing down the procedure - never back.

Any suggestions for improvement... let 'em rip.

Thanks

Steve

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Is this some sort of recurring stored proc?

Also I'm already gonna say my conclusion. Avoid gotos unless it's for err handling. Just go with a bunch of well documented / separated IFS.

lindss01 SSCrazy Points: 2264 More actions · Answer 5

Yes, normally I would agree If blocks are the way to go. In this case, though the normal run just passes through the entire Stored Procedure. It is a series of table loads. If I have to start it at a particular table load then I want control to pass to that step and proceed from there.

But maybe I'm rethinking that. Anyway the Case would still be nice to have Case Blocks instead of If blocks.

Keep on Codin'

Steve

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

Other Transact-SQL statements that can be used with control-of-flow language statements are:

CASE

/*...*/ (Comment)

-- (Comment)

DECLARE @local_variable

EXECUTE

PRINT

RAISERROR

This means you can use these with control-of-flow language statements. These are not control-of-flow language statements.

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 7

Allow me to throw in a small comment but a BIG advice. Modularize the Code into those pieces create as many procedures as pieces or starting points you need. Then create wrapper proc that calls them in the appropriate workflow without IFs

ex Supposed you have:

If @param = Start1 then goto start1

else If @param = Start2 then goto start2

else If @param = Start3 then goto start3

start1:

.....

start2:

....

start3:

...

can be translated into:

create proc proc_start1 ...

create proc proc_start2 ...

create proc proc_start3 ...

proc_invoke_all

begin

exec proc_start1

exec proc_start2

exec proc_start3

end

proc_invoke_23

begin

exec proc_start2

exec proc_start3

end

OR call them directly if you just need that piece

if what you need is repetitive and you need speed (who doesn't )

doit like that!

Just my $0.02

* Noel

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Just a note to clear things up (or perhaps muddy everything even more )

Transact SQL CASE is not a switch statement like it is in other languages, it is an expression.

In short that means that CASE must be able to evalute to true or false, then it returns (as described in BOL)

Result Types

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Now, there's two small words hidden in here that makes all the differences in the world when working with CASE, and it's good to know about.

Whenever you write a CASE in either of the ways possible, what is always true is that it will... (quote comes again)

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

What this means is simply that a CASE expression can only return one and only one datatype. If you don't explicitly cast or convert, then the type returned will be determined by the highest precedence type. If the value to be returned cannot be implicitly converted to that type, the statement will then fail.

This is by far the most common reason when you encounter seemingly 'strange' conversion errors from CASE expressions.

/Kenneth