UPDATE for multiple tables


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: UPDATE for multiple tables

  1. #1
    Kay Guest

    UPDATE for multiple tables


    I am trying to write a SP to update multiples tables. GrantProposal is the
    main table with the Primary Key as GRANTID (set as IDENTITY)in the FundingSource,
    Applicant, GrantAwarded, Project tables. The Project Table also has an IDENTIY
    for ProjectID when a GRANTID is inserted. This ProjectID is then needed for
    the BUDGET Table.

    The following is the code. I am getting must declare @intGrantID. I am also
    unsure if I did the inserts into the other tables correct.

    thanks in advance in taking a look.

    CREATE PROCEDURE sp_UpdateGrant
    (
    @intAgencyID int,
    @intFY int,
    @chvUnusedGrantNum varchar(15),
    @chvGrantTitle varchar(75),
    @chvDueDate varchar(10),
    @intStatus int,
    @mnyAmtRequested money,
    @chvGrantStatusNotes varchar(500),
    @chvGrantName varchar(50),
    @intGrantType int,
    @intAwardType int,
    @intCFDA int,
    @intReview int,
    @chvFSNotes varchar(500),
    @chvFname varchar (50),
    @chvLName varchar(50),
    @chvContactPhone varchar(50),
    @chvContactEmail varchar(50),
    @chvGrantNumber varchar(12),
    @mnyAmtAwarded money,
    @intIFAS int,
    @intPaymentMechanism int,
    @chvFedFundAcct varchar(23),
    @chvCloseOutDate varchar(10),
    @chvAwardNotes varchar(500),
    @chvOther varchar(250),
    @chvProjectTitle varchar(250),
    @chvDirector varchar (50),
    @dtmStartDate smalldatetime,
    @dtmEndDate smalldatetime,
    @chvCity1 varchar(50),
    @intCountyAffected int,
    @intStateIL int,
    @intStateIA int,
    @chvOtherAffected varchar(50),
    @chvPrimaryActivity varchar(500),
    @chvDescription varchar(250),
    @intStaffingLevel int,
    @chvStateInitiative varchar(500),
    @intTechnologyRelated int,
    @intITStdRadio int,
    @chvITStd varchar(250),
    @intROIRadio int,
    @chvRoi varchar(250),
    @chvPartners varchar(250),
    @chvApprovedBy varchar(50),
    @chvProjectNotes varchar(500),
    @intContact1 int,
    @intContact2 int,
    @mnyEstFederalYr1 money,
    @mnyEstFederalYr2 money,
    @mnyEstFederalYr3 money,
    @mnyEstFederalYr4 money,
    @mnyEstFederalYr5 money,
    @mnyStateMatch money,
    @chvSourceOfStateMatch varchar(50),
    @intMatchAppropriated int,
    @chvMatchApprovedby varchar(50),
    @intGrandEnd int,
    @intProjectID int
    )
    as
    UPDATE grantproposal
    SET
    AgencyID=@intAgencyID,
    FY=@intFY,
    UnusedGrantNum=@chvUnusedGrantNum,
    GrantTitle=@chvGrantTitle,
    DueDate=@chvDueDate,
    Status=@intStatus,
    AmtRequested=@mnyAmtRequested,
    GrantStatusNotes=@chvGrantStatusNotes

    UPDATE FundingSource
    SET
    GrantName=@chvGrantName,
    GrantType=@intGrantType,
    AwardType=@intAwardType,
    CFDA=@intCFDA,
    Review=@intReview,
    FSNotes=@chvFSNotes


    UPDATE Applicant
    SET
    Fname=@chvFname,
    LName=@chvLName,
    ContactPhone=@chvContactPhone,
    ContactEmail=@chvContactEmail

    UPDATE GrantAwarded
    SET
    GrantNumber=@chvGrantNumber,
    AmtAwarded=@mnyAmtAwarded,
    IFAS=@intIFAS,
    PaymentMechanism=@intPaymentMechanism,
    FedFundAcct=@chvFedFundAcct,
    FinalDate1=@chvFinalDate1,
    FinalDate2=@chvFinalDate2,
    FinalDate3=@chvFinalDate3,
    FinalDate4=@chvFinalDate4,
    CloseOutDate=@chvCloseOutDate,
    AwardNotes=@chvAwardNotes,
    Other=@chvOther


    UPDATE Project
    SET
    ProjectTitle=@chvProjectTitle,
    Director=@chvDirector,
    StartDate=@dtmStartDate,
    EndDate=@dtmEndDate,
    City1=@chvCity1,
    CountyAffected=@intCountyAffected,
    StateIL=@intStateIL,
    StateIA=@intStateIA,
    OtherAffected=@chvOtherAffected,
    PrimaryActivity=@chvPrimaryActivity,
    Description=@chvDescription,
    StaffingLevel=@intStaffingLevel,
    StateInitiative=@chvStateInitiative,
    TechnologyRelated=@intTechnologyRelated,
    ITStdRadio=@intITStdRadio,
    ITStd=@chvITStd,
    ROIRadio=@intROIRadio,
    Roi=@chvRoi,
    Partners=@chvPartners,
    ApprovedBy=@chvApprovedBy,
    ProjectNotes=@chvProjectNotes,
    Contact1=@intContact1,
    Contact2=@intContact2


    UPDATE Budget
    SET
    EstFederalYr1=@mnyEstFederalYr1,
    EstFederalYr2=@mnyEstFederalYr2,
    EstFederalYr3=@mnyEstFederalYr3,
    EstFederalYr4=@mnyEstFederalYr4,
    EstFederalYr5=@mnyEstFederalYr5,
    StateMatch=@mnyStateMatch,
    SourceOfStateMatch=@chvSourceOfStateMatch,
    MatchAppropriated=@intMatchAppropriated,
    MatchApprovedby=@chvMatchApprovedby,
    StateMatchRequired=@intStateMatchRequired,
    GrandEnd=@intGrandEnd


    where GrantID= @intGrantID
    return

  2. #2
    JC Guest

    Re: UPDATE for multiple tables


    it's either you declare '@intGrantID' as part of the parameters or as a separate
    declare inside the SP :

    CREATE PROCEDURE sp_UpdateGrant (parameters...., @intGrantID int)

    or


    Declare @intGrantID Int

    you just missed it out.

    one thing more, with the update statements you have, you'll be updating all
    the records in all the listed tables except for 'Budget' because you don't
    have a criteria.

    you should have a where clause on every table unless you are really updating
    all records.

    hope i helped

    JC

    "Kay" <l.k.worth@att.net> wrote:
    >
    >I am trying to write a SP to update multiples tables. GrantProposal is the
    >main table with the Primary Key as GRANTID (set as IDENTITY)in the FundingSource,
    >Applicant, GrantAwarded, Project tables. The Project Table also has an IDENTIY
    >for ProjectID when a GRANTID is inserted. This ProjectID is then needed

    for
    >the BUDGET Table.
    >
    >The following is the code. I am getting must declare @intGrantID. I am also
    >unsure if I did the inserts into the other tables correct.
    >
    >thanks in advance in taking a look.
    >
    >CREATE PROCEDURE sp_UpdateGrant
    > (
    > @intAgencyID int,
    > @intFY int,
    > @chvUnusedGrantNum varchar(15),
    > @chvGrantTitle varchar(75),
    > @chvDueDate varchar(10),
    > @intStatus int,
    > @mnyAmtRequested money,
    > @chvGrantStatusNotes varchar(500),
    > @chvGrantName varchar(50),
    > @intGrantType int,
    > @intAwardType int,
    > @intCFDA int,
    > @intReview int,
    > @chvFSNotes varchar(500),
    > @chvFname varchar (50),
    > @chvLName varchar(50),
    > @chvContactPhone varchar(50),
    > @chvContactEmail varchar(50),
    > @chvGrantNumber varchar(12),
    > @mnyAmtAwarded money,
    > @intIFAS int,
    > @intPaymentMechanism int,
    > @chvFedFundAcct varchar(23),
    > @chvCloseOutDate varchar(10),
    > @chvAwardNotes varchar(500),
    > @chvOther varchar(250),
    > @chvProjectTitle varchar(250),
    > @chvDirector varchar (50),
    > @dtmStartDate smalldatetime,
    > @dtmEndDate smalldatetime,
    > @chvCity1 varchar(50),
    > @intCountyAffected int,
    > @intStateIL int,
    > @intStateIA int,
    > @chvOtherAffected varchar(50),
    > @chvPrimaryActivity varchar(500),
    > @chvDescription varchar(250),
    > @intStaffingLevel int,
    > @chvStateInitiative varchar(500),
    > @intTechnologyRelated int,
    > @intITStdRadio int,
    > @chvITStd varchar(250),
    > @intROIRadio int,
    > @chvRoi varchar(250),
    > @chvPartners varchar(250),
    > @chvApprovedBy varchar(50),
    > @chvProjectNotes varchar(500),
    > @intContact1 int,
    > @intContact2 int,
    > @mnyEstFederalYr1 money,
    > @mnyEstFederalYr2 money,
    > @mnyEstFederalYr3 money,
    > @mnyEstFederalYr4 money,
    > @mnyEstFederalYr5 money,
    > @mnyStateMatch money,
    > @chvSourceOfStateMatch varchar(50),
    > @intMatchAppropriated int,
    > @chvMatchApprovedby varchar(50),
    > @intGrandEnd int,
    > @intProjectID int
    > )
    >as
    > UPDATE grantproposal
    > SET
    > AgencyID=@intAgencyID,
    > FY=@intFY,
    > UnusedGrantNum=@chvUnusedGrantNum,
    > GrantTitle=@chvGrantTitle,
    > DueDate=@chvDueDate,
    > Status=@intStatus,
    > AmtRequested=@mnyAmtRequested,
    > GrantStatusNotes=@chvGrantStatusNotes
    >
    > UPDATE FundingSource
    > SET
    > GrantName=@chvGrantName,
    > GrantType=@intGrantType,
    > AwardType=@intAwardType,
    > CFDA=@intCFDA,
    > Review=@intReview,
    > FSNotes=@chvFSNotes
    >
    >
    > UPDATE Applicant
    > SET
    > Fname=@chvFname,
    > LName=@chvLName,
    > ContactPhone=@chvContactPhone,
    > ContactEmail=@chvContactEmail
    >
    > UPDATE GrantAwarded
    > SET
    > GrantNumber=@chvGrantNumber,
    > AmtAwarded=@mnyAmtAwarded,
    > IFAS=@intIFAS,
    > PaymentMechanism=@intPaymentMechanism,
    > FedFundAcct=@chvFedFundAcct,
    > FinalDate1=@chvFinalDate1,
    > FinalDate2=@chvFinalDate2,
    > FinalDate3=@chvFinalDate3,
    > FinalDate4=@chvFinalDate4,
    > CloseOutDate=@chvCloseOutDate,
    > AwardNotes=@chvAwardNotes,
    > Other=@chvOther
    >
    >
    > UPDATE Project
    > SET
    > ProjectTitle=@chvProjectTitle,
    > Director=@chvDirector,
    > StartDate=@dtmStartDate,
    > EndDate=@dtmEndDate,
    > City1=@chvCity1,
    > CountyAffected=@intCountyAffected,
    > StateIL=@intStateIL,
    > StateIA=@intStateIA,
    > OtherAffected=@chvOtherAffected,
    > PrimaryActivity=@chvPrimaryActivity,
    > Description=@chvDescription,
    > StaffingLevel=@intStaffingLevel,
    > StateInitiative=@chvStateInitiative,
    > TechnologyRelated=@intTechnologyRelated,
    > ITStdRadio=@intITStdRadio,
    > ITStd=@chvITStd,
    > ROIRadio=@intROIRadio,
    > Roi=@chvRoi,
    > Partners=@chvPartners,
    > ApprovedBy=@chvApprovedBy,
    > ProjectNotes=@chvProjectNotes,
    > Contact1=@intContact1,
    > Contact2=@intContact2
    >
    >
    > UPDATE Budget
    > SET
    > EstFederalYr1=@mnyEstFederalYr1,
    > EstFederalYr2=@mnyEstFederalYr2,
    > EstFederalYr3=@mnyEstFederalYr3,
    > EstFederalYr4=@mnyEstFederalYr4,
    > EstFederalYr5=@mnyEstFederalYr5,
    > StateMatch=@mnyStateMatch,
    > SourceOfStateMatch=@chvSourceOfStateMatch,
    > MatchAppropriated=@intMatchAppropriated,
    > MatchApprovedby=@chvMatchApprovedby,
    > StateMatchRequired=@intStateMatchRequired,
    > GrandEnd=@intGrandEnd
    >
    >
    >where GrantID= @intGrantID
    >return



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center