The other part I am working on is an INSERT.
I am trying to write a SP to INSERT a new grant into 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.

I am also unsure if I did the IDENTITY right.

thanks in advance in taking a look. (warning:there is a lot of code below)

CREATE PROCEDURE sp_InsertGrant
(
@intGrantID int=0,
@intAgencyID int,
@chvFY int,
@chvUnusedGrantNum varchar(15),
@chvGrantTitle varchar(75),
@chvDueDate varchar(10),
@intStatus int,
@mnyAmtRequested money,
@chvAwardDeterminationDate varchar(10),
@chvDateActualAward varchar(10),
@intApplicationType int,
@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),
@chvInterimDate1 varchar(10),
@chvInterimDate2 varchar(10),
@chvInterimDate3 varchar(10),
@chvInterimDate4 varchar(10),
@chvFinalDate1 varchar(10),
@chvFinalDate2 varchar(10),
@chvFinalDate3 varchar(10),
@chvFinalDate4 varchar(10),
@chvCloseOutDate varchar(10),
@chvAwardNotes varchar(500),
@chvOther varchar(250),
@chvProjectTitle varchar(250),
@chvDirector varchar (50),
@dtmStartDate smalldatetime,
@dtmEndDate smalldatetime,
@chvCity1 varchar(50),
@chvCity2 varchar(50),
@chvCity3 varchar(50),
@chvCity4 varchar(50),
@intCountyAffected int,
@intStateIL int,
@intStateMO int,
@intStateWI int,
@intStateMN int,
@intStateSD int,
@intStateNE int,
@intStateIA int,
@intca1 int,
@intca2 int,
@intca3 int,
@intca4 int,
@intca5 int,
@intcp1 int,
@intcp2 int,
@intcp3 int,
@intcp4 int,
@intcp5 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,
@mnyEstApplicantYr1 money,
@mnyEstApplicantYr2 money,
@mnyEstApplicantYr3 money,
@mnyEstApplicantYr4 money,
@mnyEstApplicantYr5 money,
@mnyEstStateYr1 money,
@mnyEstStateYr2 money,
@mnyEstStateYr3 money,
@mnyEstStateYr4 money,
@mnyEstStateYr5 money,
@mnyEstLocalYr1 money,
@mnyEstLocalYr2 money,
@mnyEstLocalYr3 money,
@mnyEstLocalYr4 money,
@mnyEstLocalYr5 money,
@mnyEstOtherYr1 money,
@mnyEstOtherYr2 money,
@mnyEstOtherYr3 money,
@mnyEstOtherYr4 money,
@mnyEstOtherYr5 money,
@mnyStateMatch money,
@chvSourceOfStateMatch varchar(50),
@intMatchAppropriated int,
@chvMatchApprovedby varchar(50),
@intSubgrant int,
@intIndirectCostRate int,
@chvStateResponseAfterward varchar(250),
@intStateMatchRequired int,
@intCash int,
@intGrandEnd int
)
as
DECLARE @iGrantID int
DECLARE @iProjectID int
set nocount on

if @intGrantID=0
BEGIN
SELECT @iGrantID=@@IDENTITY
INSERT GrantProposal
(
AgencyID,
FY,
UnusedGrantNum,
GrantTitle,
DueDate,
Status,
AmtRequested,
AwardDeterminationDate,
DateActualAward,
ApplicationType,
GrantStatusNotes
)
VALUES
(
@intAgencyID,
@chvFY,
@chvUnusedGrantNum,
@chvGrantTitle,
@chvDueDate,
@intStatus,
@mnyAmtRequested,
@chvAwardDeterminationDate,
@chvDateActualAward,
@intApplicationType,
@chvGrantStatusNotes
)

SELECT @iGrantID=@@IDENTITY
INSERT FundingSource
(
GrantName,
GrantType,
AwardType,
CFDA,
Review,
FSNotes
)
VALUES
(
@chvGrantName,
@intGrantType,
@intAwardType,
@intCFDA,
@intReview,
@chvFSNotes
)

SELECT @iGrantID=@@IDENTITY
INSERT Applicant
(
Fname,
LName,
ContactPhone,
ContactEmail,
AgencyID
)
VALUES
(
@chvFname,
@chvLName,
@chvContactPhone,
@chvContactEmail,
@intAgencyID

)

SELECT @iGrantID=@@IDENTITY
INSERT GrantAwarded
(
GrantNumber,
AmtAwarded,
IFAS,
PaymentMechanism,
FedFundAcct,
InterimDate1,
InterimDate2,
InterimDate3,
InterimDate4,
FinalDate1,
FinalDate2,
FinalDate3,
FinalDate4,
CloseOutDate,
AwardNotes,
Other
)
VALUES
(
@chvGrantNumber,
@mnyAmtAwarded,
@intIFAS,
@intPaymentMechanism,
@chvFedFundAcct,
@chvInterimDate1,
@chvInterimDate2,
@chvInterimDate3,
@chvInterimDate4,
@chvFinalDate1,
@chvFinalDate2,
@chvFinalDate3,
@chvFinalDate4,
@chvCloseOutDate,
@chvAwardNotes,
@chvOther
)

SELECT @iGrantID=@@IDENTITY
INSERT Project
(
ProjectTitle,
Director,
StartDate,
EndDate,
City1,
City2,
City3,
City4,
CountyAffected,
StateIL,
StateMO,
StateWI,
StateMN,
StateSD,
StateNE,
StateIA,
ca1,
ca2,
ca3,
ca4,
ca5,
cp1,
cp2,
cp3,
cp4,
cp5,
OtherAffected,
PrimaryActivity,
Description,
StaffingLevel,
StateInitiative,
TechnologyRelated,
ITStdRadio,
ITStd,
ROIRadio,
Roi,
Partners,
ApprovedBy,
ProjectNotes,
Contact1,
Contact2
)
VALUES
(
@chvProjectTitle,
@chvDirector,
@dtmStartDate,
@dtmEndDate,
@chvCity1,
@chvCity2,
@chvCity3,
@chvCity4,
@intCountyAffected,
@intStateIL,
@intStateMO,
@intStateWI,
@intStateMN,
@intStateSD,
@intStateNE,
@intStateIA,
@intca1,
@intca2,
@intca3,
@intca4,
@intca5,
@intcp1,
@intcp2,
@intcp3,
@intcp4,
@intcp5,
@chvOtherAffected,
@chvPrimaryActivity,
@chvDescription,
@intStaffingLevel,
@chvStateInitiative,
@intTechnologyRelated,
@intITStdRadio,
@chvITStd,
@intROIRadio,
@chvRoi,
@chvPartners,
@chvApprovedBy,
@chvProjectNotes,
@intContact1,
@intContact2

)

SELECT @iProjectID=@@IDENTITY
INSERT Budget
(
EstFederalYr1,
EstFederalYr2,
EstFederalYr3,
EstFederalYr4,
EstFederalYr5,
EstApplicantYr1,
EstApplicantYr2,
EstApplicantYr3,
EstApplicantYr4,
EstApplicantYr5,
EstStateYr1,
EstStateYr2,
EstStateYr3,
EstStateYr4,
EstStateYr5,
EstLocalYr1,
EstLocalYr2,
EstLocalYr3,
EstLocalYr4,
EstLocalYr5,
EstOtherYr1,
EstOtherYr2,
EstOtherYr3,
EstOtherYr4,
EstOtherYr5,
StateMatch,
SourceOfStateMatch,
MatchAppropriated,
MatchApprovedby,
Subgrant,
IndirectCostRate,
StateResponseAfterward,
StateMatchRequired,
Cash,
GrandEnd
)

VALUES
(
@mnyEstFederalYr1,
@mnyEstFederalYr2,
@mnyEstFederalYr3,
@mnyEstFederalYr4,
@mnyEstFederalYr5,
@mnyEstApplicantYr1,
@mnyEstApplicantYr2,
@mnyEstApplicantYr3,
@mnyEstApplicantYr4,
@mnyEstApplicantYr5,
@mnyEstStateYr1,
@mnyEstStateYr2,
@mnyEstStateYr3,
@mnyEstStateYr4,
@mnyEstStateYr5,
@mnyEstLocalYr1,
@mnyEstLocalYr2,
@mnyEstLocalYr3,
@mnyEstLocalYr4,
@mnyEstLocalYr5,
@mnyEstOtherYr1,
@mnyEstOtherYr2,
@mnyEstOtherYr3,
@mnyEstOtherYr4,
@mnyEstOtherYr5,
@mnyStateMatch,
@chvSourceOfStateMatch,
@intMatchAppropriated,
@chvMatchApprovedby,
@intSubgrant,
@intIndirectCostRate,
@chvStateResponseAfterward,
@intStateMatchRequired,
@intCash,
@intGrandEnd
)
end
else
BEGIN
SET Identity_Insert GrantProposal ON
SET Identity_Insert Project ON

if @@Error = 0

BEGIN
INSERT GrantProposal
(
GrantID,
AgencyID,
FY,
UnusedGrantNum,
GrantTitle,
DueDate,
Status,
AmtRequested,
AwardDeterminationDate,
DateActualAward,
ApplicationType,
GrantStatusNotes
)

VALUES
(
@intGrantID,
@intAgencyID,
@chvFY,
@chvUnusedGrantNum,
@chvGrantTitle,
@chvDueDate,
@intStatus,
@mnyAmtRequested,
@chvAwardDeterminationDate,
@chvDateActualAward,
@intApplicationType,
@chvGrantStatusNotes
)

SELECT @iGrantID=@@IDENTITY
INSERT FundingSource
(
GrantName,
GrantType,
AwardType,
CFDA,
Review,
FSNotes
)
VALUES
(
@chvGrantName,
@intGrantType,
@intAwardType,
@intCFDA,
@intReview,
@chvFSNotes
)

SELECT @iGrantID=@@IDENTITY
INSERT Applicant
(
Fname,
LName,
ContactPhone,
ContactEmail,
AgencyID
)
VALUES
(
@chvFname,
@chvLName,
@chvContactPhone,
@chvContactEmail,
@intAgencyID

)

SELECT @iGrantID=@@IDENTITY
INSERT GrantAwarded
(
GrantNumber,
AmtAwarded,
IFAS,
PaymentMechanism,
FedFundAcct,
InterimDate1,
InterimDate2,
InterimDate3,
InterimDate4,
FinalDate1,
FinalDate2,
FinalDate3,
FinalDate4,
CloseOutDate,
AwardNotes,
Other
)
VALUES
(
@chvGrantNumber,
@mnyAmtAwarded,
@intIFAS,
@intPaymentMechanism,
@chvFedFundAcct,
@chvInterimDate1,
@chvInterimDate2,
@chvInterimDate3,
@chvInterimDate4,
@chvFinalDate1,
@chvFinalDate2,
@chvFinalDate3,
@chvFinalDate4,
@chvCloseOutDate,
@chvAwardNotes,
@chvOther
)

SELECT @iGrantID=@@IDENTITY
INSERT Project
(
ProjectTitle,
Director,
StartDate,
EndDate,
City1,
City2,
City3,
City4,
CountyAffected,
StateIL,
StateMO,
StateWI,
StateMN,
StateSD,
StateNE,
StateIA,
ca1,
ca2,
ca3,
ca4,
ca5,
cp1,
cp2,
cp3,
cp4,
cp5,
OtherAffected,
PrimaryActivity,
Description,
StaffingLevel,
StateInitiative,
TechnologyRelated,
ITStdRadio,
ITStd,
ROIRadio,
Roi,
Partners,
ApprovedBy,
ProjectNotes,
Contact1,
Contact2
)
VALUES
(
@chvProjectTitle,
@chvDirector,
@dtmStartDate,
@dtmEndDate,
@chvCity1,
@chvCity2,
@chvCity3,
@chvCity4,
@intCountyAffected,
@intStateIL,
@intStateMO,
@intStateWI,
@intStateMN,
@intStateSD,
@intStateNE,
@intStateIA,
@intca1,
@intca2,
@intca3,
@intca4,
@intca5,
@intcp1,
@intcp2,
@intcp3,
@intcp4,
@intcp5,
@chvOtherAffected,
@chvPrimaryActivity,
@chvDescription,
@intStaffingLevel,
@chvStateInitiative,
@intTechnologyRelated,
@intITStdRadio,
@chvITStd,
@intROIRadio,
@chvRoi,
@chvPartners,
@chvApprovedBy,
@chvProjectNotes,
@intContact1,
@intContact2

)

SELECT @iProjectID=@@IDENTITY
INSERT Budget
(
EstFederalYr1,
EstFederalYr2,
EstFederalYr3,
EstFederalYr4,
EstFederalYr5,
EstApplicantYr1,
EstApplicantYr2,
EstApplicantYr3,
EstApplicantYr4,
EstApplicantYr5,
EstStateYr1,
EstStateYr2,
EstStateYr3,
EstStateYr4,
EstStateYr5,
EstLocalYr1,
EstLocalYr2,
EstLocalYr3,
EstLocalYr4,
EstLocalYr5,
EstOtherYr1,
EstOtherYr2,
EstOtherYr3,
EstOtherYr4,
EstOtherYr5,
StateMatch,
SourceOfStateMatch,
MatchAppropriated,
MatchApprovedby,
Subgrant,
IndirectCostRate,
StateResponseAfterward,
StateMatchRequired,
Cash,
GrandEnd
)

VALUES
(
@mnyEstFederalYr1,
@mnyEstFederalYr2,
@mnyEstFederalYr3,
@mnyEstFederalYr4,
@mnyEstFederalYr5,
@mnyEstApplicantYr1,
@mnyEstApplicantYr2,
@mnyEstApplicantYr3,
@mnyEstApplicantYr4,
@mnyEstApplicantYr5,
@mnyEstStateYr1,
@mnyEstStateYr2,
@mnyEstStateYr3,
@mnyEstStateYr4,
@mnyEstStateYr5,
@mnyEstLocalYr1,
@mnyEstLocalYr2,
@mnyEstLocalYr3,
@mnyEstLocalYr4,
@mnyEstLocalYr5,
@mnyEstOtherYr1,
@mnyEstOtherYr2,
@mnyEstOtherYr3,
@mnyEstOtherYr4,
@mnyEstOtherYr5,
@mnyStateMatch,
@chvSourceOfStateMatch,
@intMatchAppropriated,
@chvMatchApprovedby,
@intSubgrant,
@intIndirectCostRate,
@chvStateResponseAfterward,
@intStateMatchRequired,
@intCash,
@intGrandEnd
)



SET IDENTITY_INSERT GrantProposal OFF
SET IDENTITY_INSERT Project OFF
END
END
SELECT @@error as result
set nocount off