Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

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.
0
GordonPrince
Asked:
GordonPrince
2 Solutions
 
devlab2012Commented:
Use the statement:

update table_name set ID = ID + 1 OUTPUT inserted.ID
0
 
Paul_Harris_FusionCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now