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

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)