Solved

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

Posted on 2010-11-09
2
656 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now