What is wrong with this Case Statement (Nested)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What is wrong with this Case Statement (Nested)

Author

Topic

mj76
Starting Member

8 Posts

Posted - 2003-01-23 : 15:27:34

I am trying to a nested case statement, which I have done before, only now I am getting errors in syntax, I can't seem to find them.

Below is the code for the Proc, and the error output

All Variables used have previously been declared, so they are omitted for the sake of space

///////////////////////////////

Declare @Blah float

Case When @NumberUp <> 0 then -- Line 49

Case When @Quantity / @NumberUP < 10000 then

Case When @NumberUp <= 2 then
Set @Spoilage = @EstPSpoil1
End --Line 56

Case when @NumberUp >=3 and @NumberUp <=6 then
Set @Spoilage = @EstPSpoil4
End --Line 60

Case When @Numberup >=7 then
Set @Spoilage = @EstPSpoil7
End --Line 64
End

End

|||||||||||||||||||||||||||

Server: Msg 156, Level 15, State 1, Procedure up_CalcPrinterSpoilage2, Line 49
Incorrect syntax near the keyword 'Case'.

Server: Msg 156, Level 15, State 1, Procedure up_CalcPrinterSpoilage2, Line 56
Incorrect syntax near the keyword 'End'.

Server: Msg 156, Level 15, State 1, Procedure up_CalcPrinterSpoilage2, Line 60
Incorrect syntax near the keyword 'End'.

Server: Msg 156, Level 15, State 1, Procedure up_CalcPrinterSpoilage2, Line 64 Incorrect syntax near the keyword 'End'.

//////////////////

Nothing is jumping out at me, I've done nested Case before, with no errors, this is probably something I'm over looking. Appreciate any help I get

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-23 : 15:37:44

CASE is an expression, not a control-of-flow construct like IF...THEN. It must be used to return an expression, not execute commands. You should also try to write your CASE expression so that they do NOT have to be nested, it will be much easier to follow:

DECLARE @blah float
SET @Spoilage=CASE WHEN @NumberUp=0 THEN @Spoliage
WHEN @Quantity/@NumberUp>=10000 THEN @Spoilage
WHEN @NumberUp<=2 THEN @EstPSpoil1
WHEN @NumberUp<=6 THEN @EstPSpoil4
ELSE @EstPSpoil7 --@NumberUp>=7
END

mj76
Starting Member

8 Posts

Posted - 2003-01-23 : 15:40:29

<Farting Noise>

That was my brain.

After looking at it for a second, I figured out pretty much what you just posted.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources