Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: updating tables from a table variable

    I have a table variable into which I insert the results of a select statement. Based on the records held in the table variable I then want to update a field in one table and insert the records in the table variable into another table.

    This works fine in a self contained test:

    declare @table table(electionchangeid int)
    declare @anothertable table(ID int)

    insert into @table(electionchangeid) values(1036276)

    update electionchange
    set exportdate = GETDATE()
    from electionchange ec
    join @table t on t.electionchangeid = ec.electionchangeid

    insert into @anothertable
    select * from @table

    But does not work within my sp .... (see next post).

    It doesn't generate an error. It just doesn't update or insert any records. I would think that it was a scope issue, except that I can do a select on the table variable and see that it does contain records.

    I would be very interested to hear people's thoughts on this.

    Regards
    Emma

  2. #2
    Join Date
    Dec 2004
    Posts
    2

    Here is the sp (abridged version)

    CREATE Procedure dbo.Export_51_0C
    @CompanyID INT,
    @SCE CHAR(1)= NULL,
    @Site VARCHAR(5) = NULL

    AS

    BEGIN -- sp


    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @Err INT
    DECLARE @Date AS DATETIME
    DECLARE @Enrolled TABLE (Enrolment INT, ParticipationPSAID INT)
    DECLARE @LatestChanges TABLE (ParticipationPSAID INT, MaxDate DATETIME)
    DECLARE @ReturnTable TABLE (ParticipationPSAID INT NULL,
    CompanyCode CHAR(12) NULL,
    ElectionChangeID INT NULL,
    EmployeeID INT NULL)

    -- Only export Cancellations where the enrolment has already been exported

    SET @Date = GETDATE()

    INSERT INTO
    @Enrolled(Enrolment, ParticipationPSAID)
    SELECT
    'Enrolment' = MIN(EC.ElectionChangeID),
    PPSA.ParticipationPSAID
    FROM
    ElectionChange EC
    INNER JOIN ParticipationPSA PPSA ON EC.ParticipationPSAID = PPSA.ParticipationPSAID
    INNER JOIN Enrolment EN ON PPSA.EnrolmentID = EN.EnrolmentID
    INNER JOIN EligibleEmployee EE ON EN.EligibleEmployeeID = EE.EligibleEmployeeID
    INNER JOIN Employee E ON EE.EmployeeID = E.EmployeeID
    INNER JOIN PayrollUnit P ON E.PayrollUnitID = P.PayrollUnitID
    INNER JOIN Company C ON P.CompanyID = C.CompanyID

    GROUP BY
    PPSA.ParticipationPSAID,
    EC.ExportDate,
    C.CompanyID
    HAVING
    (EC.ExportDate IS NOT NULL)
    AND
    (C.CompanyID = @CompanyID)

    -- Only look at the latest change records
    INSERT INTO
    @LatestChanges (ParticipationPSAID, MaxDate)
    SELECT
    PPSA.ParticipationPSAID,
    'MaxDate' = MAX(ChangeDate)
    FROM
    ElectionChange EC
    INNER JOIN ParticipationPSA PPSA ON EC.ParticipationPSAID = PPSA.ParticipationPSAID
    INNER JOIN Enrolment EN ON PPSA.EnrolmentID = EN.EnrolmentID
    INNER JOIN EligibleEmployee EE ON EN.EligibleEmployeeID = EE.EligibleEmployeeID
    INNER JOIN Employee E ON EE.EmployeeID = E.EmployeeID
    INNER JOIN PayrollUnit P ON E.PayrollUnitID = P.PayrollUnitID
    INNER JOIN Company C ON P.CompanyID = C.CompanyID

    GROUP BY
    PPSA.ParticipationPSAID, EC.ExportDate, C.CompanyID, EC.ChangeDate
    HAVING
    (EC.ExportDate IS NULL)
    AND
    (C.CompanyID = @CompanyID)
    AND
    (EC.ChangeDate < @Date)




    INSERT INTO @ReturnTable
    (ParticipationPSAID,
    CompanyCode,
    EmployeeID,
    ElectionChangeID)
    SELECT
    PSA.ParticipationPSAID,
    Cm.CompanyCode,
    E.EmployeeID,
    EC.ElectionChangeID,

    FROM Employee E
    JOIN PayrollUnit P
    ON P.PayrollUnitID = E.PayrollUnitID
    JOIN PayrollFreqCurr PFC
    ON PFC.PayrollFreqCurrID = E.PayrollFreqCurrID
    JOIN PayrollFrequency PF
    ON PF.PayrollFrequencyId = PFC.PayrollFrequencyID
    JOIN EligibleEmployee EE
    ON EE.EmployeeID = E.EmployeeID
    JOIN AwardSecurity Asec
    ON Asec.AwardSecurityID = EE.AwardSecurityID
    JOIN Award A
    ON A.AwardID = Asec.AwardID
    JOIN Security S
    ON S.securityID = Asec.SecurityID
    JOIN PlanProduct PP
    ON PP.PlanProductId = A.PlanProductID
    JOIN Company CM
    ON Cm.CompanyID = P.CompanyID
    JOIN Enrolment EN
    ON EN.EligibleEmployeeID = EE.EligibleEmployeeID
    JOIN ParticipationPSA PSA
    ON PSA.EnrolmentID = EN.EnrolmentID
    JOIN ElectionChange EC
    ON EC.ParticipationPSAID = PSA.ParticipationPSAID

    --This join is to make sure we send only the most recent change.
    JOIN @LatestChanges LC
    ON LC.ParticipationPSAID = EC.ParticipationPSAID
    AND LC.Maxdate = EC.ChangeDate

    --This join is to make sure we only send cancellations for exported enrolments.
    JOIN @Enrolled ED
    ON EC.ParticipationPSAID = ED.ParticipationPSAID


    WHERE P.CompanyID = @CompanyID
    AND PSA.ParticipationStatusID IN (3,5,8,10)
    AND EC.ExportDate IS NULL
    AND P.TransmissionSite = ISNULL(@Site, P.TransmissionSite)

    ORDER BY MLAccountNumber

    BEGIN TRAN

    UPDATE ElectionChange
    SET ExportDate = GETDATE()
    FROM ElectionChange EC
    JOIN @ReturnTable RT ON RT.ElectionChangeID = EC.ElectionChangeID

    SET @Err = @@ERROR
    IF @Err > 0
    BEGIN
    ROLLBACK
    RETURN 1
    END


    -- Update the ElectionChange510Export table with the records which were actually exported
    INSERT INTO ElectionChange510Export
    (ElectionChangeID,
    ParticipationPSAID,
    companycode,
    EmployeeID)
    SELECT
    ElectionChangeID,
    ParticipationPSAID,
    companycode,
    EmployeeID)
    FROM
    @ReturnTable RT


    SET @Err = @@ERROR
    IF @Err > 0
    BEGIN
    ROLLBACK
    RETURN 1
    END
    COMMIT


    -- Drop the ParticipationPSAID
    SELECT CompanyCode,
    EmployeeID,
    ElectionChangeID)
    FROM
    @ReturnTable

    SET NOCOUNT OFF

    END -- sp

Posting Permissions

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