INSERT for multiple tables with IDENTITY


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: INSERT for multiple tables with IDENTITY

  1. #1
    Kay Guest

    INSERT for multiple tables with IDENTITY


    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

  2. #2
    JC Guest

    Re: INSERT for multiple tables with IDENTITY


    hi!

    first things first, get the latest generated identity after an 'Insert' or
    'Select Into' statement otherwise it will return null. next, i noticed that
    you're using '@iGrantID' and '@iProjectID' to get the last generated identity
    but you are not using it. if you need to get the latest generated identity
    value and use it for the next statement try something like this :

    <Insert Statement> or <Select Into statement>
    Select <variable> = @@Identity

    Insert into <table> (fields....,<field where to insert the identity value>)

    Values (values..., <variable>)

    hope this helps! you can email me if this is too vague..

    JC



    "Kay" <l.k.worth@att.net> wrote:
    >
    >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



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