Results 1 to 2 of 2

Thread: SP construction

  1. #1
    Join Date
    May 2003
    Location
    Sydney AU
    Posts
    15

    Unanswered: SP construction

    Howdy Folks,

    I'm trying to learn "fast" about SP's & the best way to construct them. Can someone please assist by reviewing these SP's and sharing their opinion ???

    I think the developer is meant to declare local variables for his sub queries - but would like a second / third opinion to ensure I have understood correctly.

    The developer has already rewritten the SP with declared variables - but it still looks ugly in comparison to the examples in the manuals I've been reading.

    Any assistance would be greatly appreciated.

    Suze.

    Original SP:
    CREATE PROCEDURE CTC_GetTradingPartner
    (
    @TPRid uniqueidentifier
    )
    AS

    SELECT TPRid, TPRName, TPRAddress1, TPRAddress2, TPRAddress3, TPRLegalName, TPRPostalAddress1, TPRPostalAddress2, TPRCity, TPRPostCode, TPRPhone, TPRContactName, TPRMobile, TPRBankAcc, TPRParentTPRid, TPRTPTid, TPRQuickKey, TPRCOPid, TPRFax, TPRWebSite, TPREmail, TPRFTP, TPRInactive, TPRIsCustomer, TPRIsSupplier, TPRIsUs, TPRIsOurBranch, TPROwnerBRNid, TPRBranchPrefix, TPRBranchNextJobNbr, TPRBranchConcreteDensity, TPRBranchGLCode, TPRLastUpdate, TPRGSTCode, TPRDespatchPrinter, TPRRecipientBillingApproved, TPRPaymentType, TPRConcrete, TPRFBL, TPRGSTExempt, TPRStaffMember, TPRAccountsContact, TPRDespatchContact, TPRDynamicsID,
    (SELECT TPRQuickKey FROM CTC_TradingPartner WHERE TPRid = TPRParentTPRid ) AS TPRParentQuickKey,
    (SELECT TPRName FROM CTC_TradingPartner WHERE TPRid = TPRParentTPRid ) AS TPRParentName
    FROM CTC_TradingPartner
    WHERE TPRid = @TPRid
    GO


    Version 2:

    CREATE PROCEDURE CTC_GetTradingPartner
    (
    @TPRid uniqueidentifier
    )
    AS

    SET ANSI_NULLS ON
    DECLARE @Parent as uniqueidentifier
    SELECT @Parent = TPRParentTPRid
    FROM CTC_TradingPartner
    WHERE TPRid = @TPRid

    SELECT TPRid, TPRName, TPRAddress1, TPRAddress2, TPRAddress3, TPRLegalName, TPRPostalAddress1, TPRPostalAddress2, TPRCity, TPRPostCode, TPRPhone, TPRContactName, TPRMobile, TPRBankAcc, TPRParentTPRid, TPRTPTid, TPRQuickKey, TPRCOPid, TPRFax, TPRWebSite, TPREmail, TPRFTP, TPRInactive, TPRIsCustomer, TPRIsSupplier, TPRIsUs, TPRIsOurBranch, TPROwnerBRNid, TPRBranchPrefix, TPRBranchNextJobNbr, TPRBranchConcreteDensity, TPRBranchGLCode, TPRLastUpdate, TPRGSTCode, TPRDespatchPrinter, TPRRecipientBillingApproved, TPRPaymentType, TPRConcrete, TPRFBL, TPRGSTExempt, TPRStaffMember, TPRAccountsContact, TPRDespatchContact, TPRDynamicsID,
    (SELECT TOP 1 TPRQuickKey FROM CTC_TradingPartner WHERE TPRid = @Parent ) AS TPRParentQuickKey,
    (SELECT TOP 1 TPRName FROM CTC_TradingPartner WHERE TPRid = @Parent ) AS TPRParentName
    FROM CTC_TradingPartner
    WHERE TPRid = @TPRid
    GO

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    This is great stuff:
    'trying to learn "fast" about SP's' followed by 'I think the developer is meant' closed by a 'The developer has already rewritten'.

    Did you run them through the analyzer?

    oh: why do you think that subqueries need to work with local variables?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •