Solved

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

Posted on 2010-11-09
2
671 Views
Last Modified: 2012-05-10
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
Comment
Question by:GordonPrince
2 Comments
 
LVL 13

Accepted Solution

by:
devlab2012 earned 200 total points
ID: 34094203
Use the statement:

update table_name set ID = ID + 1 OUTPUT inserted.ID
0
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 50 total points
ID: 34094392
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with simplifying SQL 6 54
SQL Server: SNAPSHOT replication to include a newly added table. 2 30
SQL Error - Query 6 41
Parse this column 6 27
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question