Stored Procedure: Incorrect syntax near 'END'

From: Phil Davy (philipDOTdavyATcatlin[/DOT;/}com)
Date: 05/25/04 

Date: Tue, 25 May 2004 07:01:02 -0700

Hi all

been tearing my hair out over this problem. I have a relatively straightforward SP which gets parameters passed into it using a cursor.

However whenever I run the code below, it keeps coming up with Incorrect Syntax near END. All my BEGIN and END statements are paired up and I am at a loss to establish what is wrong.

Could someone please help? if code formatting has gone wrong below, more than happy to email to you.

Thanks
Phil

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BrokerAlterations]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROC [dbo].[BrokerAlterations]
GO

CREATE PROC BrokerAlterations

@TierLevel int(1),
@ActionType varchar(255),
@BrokerSuperParentNameExisting varchar(255),
@BrokerSuperParentName varchar(255),
@BrokerParentNameExisting varchar(255),
@BrokerParentName varchar(255),
@BrokerNumber varchar(255)

AS

BEGIN
        
        IF @TierLevel = 1 -- Mapping FROM Broker Group to Broker
        
        BEGIN
                
                /*
                DELETE
                
                • Only proceed if Broker Group exists in ogp under classifications of Broker Group AND ptyParty; AND has no dependent Brokers else print a Warning
                • DELETE Broker Group FROM ogp, under classifications of Broker Group AND ptyParty. DELETE corresponding entries FROM ogm.
                */
        
                IF @ActionType = 'D'
                BEGIN
        
                        IF EXISTS (SELECT * FROM ogpObjGroup WHERE ogpObjGroup.ogpDscrip = @BrokerParentName)
                        AND ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group')
                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party') -- Does the broker group name already exist?
                        AND @BrokerParentName IS NOT NULL -- The new broker name is not null
                        AND NOT EXISTS (
                                                SELECT ogmMbrSID
                                                FROM ogpObjGroup INNER JOIN ogmObjGroupMbrshp
                                                ON ogmObjGroupMbrshp.ogmObjGroupSIDogp = ogpObjGroup.ogpSID
                                                        WHERE ogpObjGroup.ogpDscrip = @BrokerParentName
                                                        AND ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group')
                                                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party') -- Does the broker group have any dependent broker(s)?
                                           )
                                                        
                        BEGIN
        
                                DELETE FROM ogpObjGroup
                                        WHERE ogpObjGroup.ogpDscrip = @BrokerParentName
                                        AND ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group') -- i.e. clsName = Broker Classification. Ensure deleting FROM Broker classificatiON
                                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party') -- i.e. otpDscrip = Broker Group. Ensure deleting ON Broker Groups ONLY
        
                                DELETE FROM ogmObjGroupMbrshp -- DELETE records FROM table that maps broker groups to brokers
                                        WHERE ogmObjGroupMbrshp.ogmObjGroupSIDogp in (SELECT ogpSID FROM ogpObjGroup WHERE ogpDscrip = @BrokerParentName)
        
                        END
        
                        ELSE PRINT ('Broker Group does not already exist and/or Broker Group has dependent Broker(s); record will not be deleted')
        
                END
        
                /*
                UPDATE
                
                • Only proceed if Broker Group exists, else print Information
                • UPDATE Broker Group Name in ogp under classifications of Broker Group AND ptyParty. Note: no links in ogm will need to change, AS mapping will point to same Broker Group.
                */
        
        
                IF @ActionType = 'U'
        
                BEGIN
        
                        IF EXISTS (SELECT * FROM ogpObjGroup WHERE ogpObjGroup.ogpDscrip = @BrokerParentNameExisting) -- Does the broker group name already exist?
                        AND ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group')
                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party')
                        AND @BrokerParentNameExisting IS NOT NULL -- The existing broker name is not null
                        AND @BrokerParentName IS NOT NULL -- The new broker name is not null
        
                        BEGIN
        
                                UPDATE ogpObjGroup
                                SET ogpObjGroup.ogpName = NULL, ogpObjGroup.ogpDscrip=@BrokerParentName
                                        WHERE ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group') -- i.e. clsName = Broker Classification. Ensure deleting FROM Broker classificatiON
                                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party') -- i.e. otpDscrip = Broker Group. Ensure deleting ON Broker Groups only
        
                        END
        
                        ELSE PRINT ('Broker Group does not exist: record will not be UPDATEd')
        
                END
        
        
                /*
                INSERT
                
                • Only proceed if Broker Group does not exist in classificatiON of Broker Group AND ptyParty
                • Only proceed if Broker Group is being inserted along with NON-NULL AND Approved Broker Numbers
                • Insert new Broker Group(s) in ogm AND associated Broker(s) Numbers in ogp
                */
        
        
                IF @ActionType = 'I'
        
                BEGIN
                        
                        IF NOT EXISTS (SELECT * FROM ogpObjGroup WHERE ogpObjGroup.ogpDscrip = @BrokerParentName) -- Broker Parent Name does NOT already exist
                        AND ogpObjGroup.ogpPurpSIDcls IN (SELECT clsSID FROM clsClasn WHERE clsName = 'Broker Group') -- i.e. clsName = Broker Classification. Ensure deleting FROM Broker classificatiON
                        AND ogpObjGroup.ogpObjTypeSIDotp IN (SELECT otpSID FROM otpObjType WHERE otpDscrip = 'Party') -- i.e. otpDscrip = Broker Group. Ensure deleting ON Broker Groups only
                        AND @BrokerParentName IS NOT NULL
                        AND EXISTS ( -- this script below filters out Approved broker numbers
                        

                                SELECT pyiPartyIdntfr.pyiIdntfrContnt AS BkrNmbr
                                
                                FROM ptyParty INNER JOIN pyiPartyIdntfr ON ptyParty.ptySID = pyiPartyIdntfr.pyiPartySIDpty
                                                INNER JOIN prlPartyRole ON prlPartyRole.prlPartySIDpty = ptyParty.ptySID
                                                INNER JOIN inmInsMkt ON prlPartyRole.prlContxtInstSID = inmInsMkt.inmSID
                                                INNER JOIN prtPartyRoleType ON prlPartyRole.prlPartyRoleTypeSIDprt = prtPartyRoleType.prtSID
                                                INNER JOIN idsIdntfrScheme ON pyiPartyIdntfr.pyiIdntfrSchemeSIDids = idsIdntfrScheme.idsSID
                                
                                
                                WHERE prtPartyRoleType.prtName = 'Approved Broker' -- Identifies Approved Brokers
                                AND inmInsMkt.inmName = 'Lloyds' -- Lloyd's Brokers
                                AND idsIdntfrScheme.idsName = 'Broker number' -- To return Broker Numbers
                                AND pyiPartyIdntfr.pyiIdntfrContnt = @BrokerNumber)
        
        
                        BEGIN
        
                        INSERT INTO ogpObjGroup(ogpName, ogpDscrip, ogpStartDate, ogpEndDate, ogpVsbltySIDcls, ogpUserOwnerSIDusr, ogpObjTypeSIDotp, ogpPurpSIDcls, ogpUpdDate, ogpModVersn, ogpUserLastUpdSIDusr, ogpInsSIDusr, ogpInsDate)
                        VALUES(NULL, @BrokerParentName, getdate(), NULL, 10475, 1, 1, 270, getdate(), 0, 0, NULL, NULL)
        
                        INSERT INTO ogmObjGroupMbrshp(ogmStartDate, ogmEndDate, ogmIsChildGroup, ogmObjGroupSIDogp, ogmMbrTable, ogmMbrSID, ogmUpdDate, ogmModVersn, ogmUserLastUpdSIDusr, ogmInsSIDusr, ogmInsDate)
                        VALUES( getdate(), NULL, 'N', @@IDENTITY, 'ptyParty', @BrokerNumber, getdate(), 1, 1, NULL, NULL)
        
                        END
        
                ELSE Print ('Broker Group already exists and/or Broker Number is not that of an Approved Broker. Record will not be inserted')
        
                END
                
        END