Stored procedure returning the result of an UPDATE

Question

I have a table that is used to store an incrementing numeric ID (of type INT). It contains a single row. The ID is incremented using a query:

UPDATE TOP(1) MyTable
WITH(TABLOCKX)
SET NextID = NextID + 1

I would like to move this into a stored procedure that returns the value that was in the NextID column before it was incremented, but am unsure how to do this using OUTPUT parameters. Any help would be appreciated.

Answer 1

You might want to add in some error checking, etc. I also assumed that the table is a one-row table.

CREATE PROCEDURE dbo.Get_New_My_Table_ID
    @current_id INT = NULL OUTPUT    -- Declared OUTPUT parameter
AS
BEGIN
    BEGIN TRANSACTION

    SELECT
        @current_id = COALESCE(next_id, 0)
    FROM
        dbo.My_Table WITH (TABLOCK, HOLDLOCK)

    UPDATE dbo.My_Table
    SET next_id = @current_id + 1

    COMMIT TRANSACTION
END
GO

To use the stored procedure:

DECLARE @my_id INT

EXEC dbo.Get_New_My_Table_ID @current_id = @my_id OUTPUT

Answer 2

In SQL2005 or later the previous values for updated values can be retrieved using the OUTPUT clause.

Given that only one value will be returned I don't know how this compares with Tom's approach. I suspect that Tom's will be faster as there is an extra read but no overhead of creating a table variable. (Edit: and of course mine still has to read the table variable as well!)

CREATE PROC blah
@OldId int OUTPUT
AS

DECLARE @OldIds table( NextID int);


UPDATE TOP(1) MyTable
WITH(TABLOCKX)
SET NextID = NextID + 1
OUTPUT DELETED.NextID INTO @OldIds

SELECT @OldId = NextID
FROM @OldIds

Answer 3

for SQL Server 2005+, try:

CREATE PROCEDURE dbo.Get_New_My_Table_ID
    @current_id INT = NULL OUTPUT    -- Declared OUTPUT parameter
AS
BEGIN TRANSACTION

UPDATE TOP(1) MyTable WITH(TABLOCKX)
    SET NextID = NextID + 1
    OUTPUT DELETED.NextID

COMMIT TRANSACTION

RETURN 0
GO

the results of OUTPUT don't need to go into an actual table, it can be a result set.

test it out:

declare @MyTable table (NextID int)
INSERT INTO @MyTable VALUES (1234)


SELECT 'BEFORE',* FROM @MyTable

PRINT '------------<<<<UPDATE>>>>---------'
UPDATE TOP(1) @MyTable
    SET NextID = NextID + 1
    OUTPUT DELETED.NextID
PRINT '------------<<<<UPDATE>>>>---------'

SELECT 'AFTER',* FROM @MyTable

OUTPUT:

(1 row(s) affected)
       NextID
------ -----------
BEFORE 1234

(1 row(s) affected)

------------<<<<UPDATE>>>>---------
NextID
-----------
1234

(1 row(s) affected)

------------<<<<UPDATE>>>>---------
      NextID
----- -----------
AFTER 1235

(1 row(s) affected)