UPDATE for multiple tables

[Kay]

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

[JC]

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