Syntax error in case statement

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

Transact-SQL (2000)

Syntax error in case statement

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 11:59:11

Hi,
I am writing a stored procedure using temporary table. One of the field in the temporary table is being updated using case statment logic. I have the following code:
UPDATE #tmpApproval1a
SET ROI_initial = CASE
WHEN ( (annualvolumeper1000 * firstyearrevenueperthousand) - (lowestmktpricemsrp * factor) + toolmsrpmarketprice) = 0 Then ROI_initial = 0
WHEN totaltoolingcost = 0 Then ROI_initial = 0
ELSE ROI_initial = (( (annualvolumeper1000 *firstyearrevenueperthousand) - (lowestmktpricemsrp * factor) + toolmsrpmarketprice) - totaltoolingcost)/ totaltoolingcost
END

However I am getting the following syntax error:
Incorrectsyntax near '='
Incorrect syntax near 'annualvolumeper1000'
Incorrect syntax near the keywork 'END'

I am not sure why I am getting the above error. Any help is appreciated. Thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-11 : 12:30:54

UPDATE #tmpApproval1a
SET ROI_initial = CASE
WHEN ( (annualvolumeper1000 * firstyearrevenueperthousand) - (lowestmktpricemsrp * factor) + toolmsrpmarketprice) = 0 Then /*ROI_initial =*/ 0
WHEN totaltoolingcost = 0 Then /*ROI_initial =*/ 0
ELSE /*ROI_initial =*/ (( (annualvolumeper1000 *firstyearrevenueperthousand) - (lowestmktpricemsrp * factor) + toolmsrpmarketprice) - totaltoolingcost)/ totaltoolingcost
END

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 13:01:48

Thanks jdaman for your help. It seems there is no necessity to put ROI_initial in the code. Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources