Nested IF Help - SQL Server Forums

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)

Nested IF Help

Author

Topic

DJSTYLi
Starting Member

1 Post

Posted - 2005-03-17 : 06:53:18

Hi,

I am trying to do a nested IF and get the following error:

Error 156: Incorrect syntax near the keyword 'END'

I have looked and looked and looked and can't see what's wrong. Can anyone help? Here is my sproc:

**********************************************

CREATE Procedure spDrawdown_RollbackTriennial_GetNewValues

(

@SubID int,
@NewLastAnnual datetime,
@NewLastEffective datetime,
@NewNextAnnual datetime,
@PensionDate datetime,
@LastTriReview datetime
)

As

-- check if renewal has occured or not
IF

@PensionDate = @NewLastAnnual
select * from sipp_memberdetails
ELSE
BEGIN
IF

@PensionDate = @NewLastAnnual
AND @NewLastAnnual = @LastTriReview

-- rollback last triennial and rollback last annual
--1. rollback last triennial
BEGIN
DELETE SIPP_DrawdownReview
FROM

(SELECT TOP 1
SDR.ReviewDate,
ISNULL(SDR.FundValue, 0) AS Fund,
SDR.MaxIncome,
SDR.MinIncome,
SDR.SDR_ID
from dbo.sipp_drawdownreview SDR
INNER JOIN dbo.SIPP_memberDetails SMD
ON SMD.mliId = SDR.mliID
where SMD.subID = @SubID
ORDER BY SDR.ReviewDate DESC)

AS RS
WHERE sipp_drawdownreview.sdr_id = RS.sdr_id

--2. rollback last annual
SELECT
@NewLastAnnual = DateAdd(year, -1, SMD.dtLastIdd_renewal),
@NewLastEffective = DateAdd(year, -1, SMD.dtLastIdd_renewalEff),
@NewNextAnnual = DateAdd(year, -1, SMD.dtNextIdd_renewal)
from dbo.sipp_memberdetails SMD
where SMD.subID = @SubID

UPDATE
SIPP_MemberDetails
SET
dtLastIdd_Renewal = @NewLastAnnual,
dtLastIdd_RenewalEff = @NewLastEffective,
dtNextIdd_Renewal = @NewNextAnnual

where subID = @SubID
END

ELSE

-- 1. rollback last annual
BEGIN
SELECT
@NewLastAnnual = DateAdd(year, -1, SMD.dtLastIdd_renewal),
@NewLastEffective = DateAdd(year, -1, SMD.dtLastIdd_renewalEff),
@NewNextAnnual = DateAdd(year, -1, SMD.dtNextIdd_renewal)
from dbo.sipp_memberdetails SMD
where SMD.subID = @SubID

UPDATE
SIPP_MemberDetails
SET
dtLastIdd_Renewal = @NewLastAnnual,
dtLastIdd_RenewalEff = @NewLastEffective,
dtNextIdd_Renewal = @NewNextAnnual

where subID = @SubID
END
END IF
END
END IF

/* set nocount on */
return
GO
************************************************

Thanks a lot if u can!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-17 : 07:34:53

sql doesn't use the 'END IF'... it only uses 'END'. And I think you have to many of them

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources