return result from "update table set ID = ID + 1"

I'm working with a database someone else created. They have a table that stores the last identity number used. I'm trying to consume numbers in the same sequence that the other application does.

Is there a way to execute an update statement and get the result back that's absolutely foolproof? I don't care what the number is, I want to add one to it, then know what the number I justed created is.
LVL 4
GordonPrinceAsked:
Who is Participating?
 
devlab2012Connect With a Mentor Commented:
Use the statement:

update table_name set ID = ID + 1 OUTPUT inserted.ID
0
 
Paul_Harris_FusionConnect With a Mentor Commented:
You can use a stored procedure
The following example could be adapted for your needs.

/* Test Data*/
Create table ID_TABLE (ID_NAME varchar(30), ID_VAL int);
Insert into ID_TABLE(ID_NAME, ID_VAL) VALUES('DEFAULT',0);

/* Create the stored procedure */
CREATE PROCEDURE dbo.GetNextID
AS
BEGIN
      DECLARE @NextID INT
      BEGIN TRANSACTION
            UPDATE ID_TABLE SET ID_VAL=ID_VAL+1 where ID_NAME='DEFAULT'
            SELECT @NextID = ID_VAL FROM ID_TABLE WHERE ID_NAME = 'DEFAULT'
      COMMIT TRANSACTION
      RETURN @NextID
END

/* Call the stored procedure */

DECLARE @MyID INT;

EXEC @MyID = dbo.GetNextID;

Select @MyID;
0
All Courses

From novice to tech pro — start learning today.