CODEWITHSUNDEEP
sql-serverstored-procedures
Potatan
Potatan

Reputation: 29

Stored procedure "forgetting" field between update and insert statement

We have a stored procedure that is used to UPDATE a table with a value calculated from an existing column.

I am trying to amend the stored procedure to also INSERT a row into a different table, using that same column's value but the column is being rejected by the parser as an invalid column name.

Here is a condensed version of the code. As originally supplied the sequence_no is known to the stored procedure and ends up in reference_no. i.e. the UPDATE works but the INSERT fails.

ALTER PROCEDURE [dbo].[update_references]
AS
    -- Original contents:
    UPDATE table1
    SET reference_no = sequence_no
    FROM table1 t1 WITH (NOLOCK)
    LEFT OUTER JOIN proptable p1 WITH (NOLOCK) ON t1.checkval = p1.checkval
    WHERE p1.fruit = 'apple'

    -- I have added the INSERT 
    INSERT INTO table2 (next_seq_no)
    VALUES (sequence_no)

The sequence_no is underlined in red in SSMS.

Upvotes: 1

Views: 61

Answers (1)

Martin Smith
Martin Smith

Reputation: 453426

The insert statement in your code knows nothing about the previous update so you can't reference random columns from that and expect them to still be in scope. The easiest way of doing this is using the OUTPUT clause.

UPDATE table1
SET reference_no = sequence_no
OUTPUT INSERTED.reference_no INTO table2 (next_seq_no)
FROM table1 t1
LEFT OUTER JOIN proptable p1 ON t1.checkval = p1.checkval
WHERE p1.fruit = 'apple'

Upvotes: 1

Related Questions