SQL Server 2000 If statement in stored procedure

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)

SQL Server 2000 If statement in stored procedure

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-12-26 : 14:34:44

I am trying to add an if statement to where the year grouped = 2000 I subtract $3,500,000 from the Expense. I keep getting error by then clause. I have the logic but I think the syntax is off? mahalo, for your help :)

CREATE PROCEDURE [dbo].[SP_YearTotalsSupport] AS
SELECT YEAR(PlanUpgrade) AS Expr1, SUM(Expense) AS Expr2
FROM testimport
GROUP BY YEAR(PlanUpgrade), Inhouse
HAVING (SUM(Expense) > 0) and Inhouse = 'Y'
ORDER BY YEAR(PlanUpgrade);

If YEAR(planupgrade) = 2000 then sum(expense) = (sum(expense) - 3500000)
End If;
return;
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 14:39:15

Remove the IF statement, then add a CASE to your SELECT:

SELECT YEAR(PlanUpgrade) AS Expr1,
CASE WHEN YEAR(PlanUpgrade) = 2000 THEN SUM(Expense) - 3500000 ELSE SUM(Expense) END AS Expr2
FROM testimport
GROUP BY YEAR(PlanUpgrade), Inhouse
HAVING (SUM(Expense) > 0) and Inhouse = 'Y'
ORDER BY YEAR(PlanUpgrade);

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources