Re: Stored Procedure: Incorrect syntax near 'END'

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/25/04 

Date: Tue, 25 May 2004 10:04:13 -0400

You have a mismatched number of BEGIN and END statements.

If you use sensible indenting for your code, you will quickly see where any
ENDs are missing. 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Phil Davy" <philipDOTdavyATcatlin[/DOT;/}com> wrote in message
news:AC22879C-EE90-4404-AE07-F78143132AFC@microsoft.com...
> 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

Relevant Pages

  • Re: How do I query companies not having members w/certain level of
    ... Save each query with the name I have given below each query. ... SELECT Contact.ContactID, "Managing Broker" AS NewTitle ... FROM qCompanyManyAssocBelow INNER JOIN Contact ON ... mkting materal to persons (members) within R/E offices that have titles ...
    (microsoft.public.access.queries)
  • Re: Query for Parent containing only ONE type of child
    ... It will return a company that has two members, one with a title of "Managing ... Broker" and the other with "Broker Associate". ... Perhaps better performance for Jet: ... INNER JOIN Contact AS T1 ...
    (microsoft.public.access.queries)
Loading