SQL Question

[BritTeacher]

How do I write a Stored Procedure that increments a value in a column and retrieves that value without any other instance of the same Stored Procedure (from another user) incrementing the value at the same time?

 

I was thinking of something like this, but I don't think there is a VB.NET Do Loop in SQL Server.

 

 

Do
 SELECT @ID = MyColID FROM tblTest WHERE col_id=1
 @ID=@ID+1
 UPDATE tblTest SET MyColID = @ID WHERE MyColID = @ID-1 AND col_id=1
Loop While @@RowCount=0

 

The update line checks that no one else has modified the row in the since the SELECT statement. If the update fails, the loop tries again.

 

Will this work? If so, how do I do the Loop in SQL Server?

[Joe Mamma]

The update line checks that no one else has modified the row in the since the SELECT statement. If the update fails, the loop tries again.

 

Will this work? If so, how do I do the Loop in SQL Server?

hmmm. . . shouldnt you use a timestamp field?

[Nerseus]

Will an Identity not work for you?

 

Typically you will do something like this:

-- Begin a tran and update so that you lock the table
BEGIN TRAN

-- Assumes only one row
UPDATE tblTest SET myColID = MAX(myColID) + 1

-- Get the value into a variable - a pure SELECT will work fine too
SELECT @SomeVar = MAX(myColID) FROM tblTest

COMMIT TRAN

 

-nerseus

[Joe Mamma]

if a field is defined as a timestamp, it is updated automatically whenever an action takes place on the record. . .

 

if the field is part of the select statement it can be used to assure no one else has changed the data. . .

 

Look up timestamp in the Transact-SQL reference and the MSDN help -

 

create table blah 
(
blahID uniqueidentifier not null default NEWID() primary key,
blahInt integer not null,
blahTS rowversion
)

insert into blah (blahInt) values(2)
insert into blah (blahInt) values(3)
select * from blah

yields:

[font=Courier New]blahID                               blahInt     blahTS             
------------------------------------ ----------- ------------------[/font]
[font=Courier New][font=Courier New]A2C9FAC1-2B2D-4CFF-B28B-1140C48FC479 2            0x0000000000002776[/font]
[font=Courier New]BAF8D651-033B-4509-AFA2-8BA42EBAA4E0 3           0x0000000000002777[/font]
[/font][font=Verdana]

[/font]now two people select ala:

select * from blah 
where blahID = 'A2C9FAC1-2B2D-4CFF-B28B-1140C48FC479'

person 1 executes:

update blah 
set blahInt = 1000 
where blahTS = 0x0000000000002776

rows affected = 1

person 2 then executes:

update blah 
set blahInt = 10000 
where blahTS = 0x0000000000002776

rows affected = 0

person 2 must requery to assure he has the latest version of the row, before updating, because person 1's update changed the timestamp/rowversion -

select * from blah 
where blahID = 'A2C9FAC1-2B2D-4CFF-B28B-1140C48FC479'

yeilds:

[font=Courier New]blahID                               blahInt     blahTS             
------------------------------------ ----------- ------------------[/font]
[font=Courier New]A2C9FAC1-2B2D-4CFF-B28B-1140C48FC479 1000        0x0000000000002779[/font][font=Verdana]

[/font]

[inzo21]

if it's an identity, you can add an output parameter to the stored procedure

 

declare

(

@value

)

 

increment you field here

set @value = @@identity.

 

the @@identity will return the last value generated automatically for the inserted record. If you are returning another feidl value other than an identity, the same method should work.

 

inzo

[BritTeacher]

I cannot use a TimeStamp, because I want to use the value to check for "<=" and ">=" as well as equality. There are other reasons as well.

 

I cannot use an auto incrementing value, because I need to use the value for updates as well as inserts (like the TimeStamp).

 

I do not think the TRANSACTION idea would work, since transactions only ensure that the whole transaction is performed in its entirety, but not necessarily as an atomic unit.

 

For example, if the following procedure is performed twice concurrently (on two threads):

 

SELECT @ID = MyColID FROM tblTest WHERE col_id=1

@ID=@ID+1

UPDATE tblTest SET MyColID = @ID WHERE col_id=1

 

While one procedure is performing @ID=@ID+1, ready to store the new value, the other procedure might be SELECTing the @ID column.

 

Do you see the problem?

 

The three statements have to be performed as one unit.

[JABE]

I cannot use a TimeStamp, because I want to use the value to check for "<=" and ">=" as well as equality

You can test timestamp fields for equality/inequality.

 

I do not think the TRANSACTION idea would work, since transactions only ensure that the whole transaction is performed in its entirety, but not necessarily as an atomic unit.

Nope, remember the 'A' in ACID? That's atomicity. You can use a REPEATABLE READ transaction isolation level.

[Joe Mamma]

and I think you are mixing two concepts row uniqiueness and data concurrency.

 

Timestamp assures that the datarow you are updating is the datarow as you want to work with it (Concurrency). . . not to be used for identity.

 

The INDENTITY modifier maintains uniqueness.

 

I believe in DKNF and strive for it whenever possible.

 

Myself, I have rarely find a need for Indentity Integers, I use GUIDS. Most of the time, My Primary keys are multi-field or domain specified single values and numbers. I then generate a Surrogate Key (GUID) to ID the row and use that as foreign key references.

 

 

You want the DB to maintain identity on its own. app developer shouldnt be concerned with it, on either the client or server side.

 

the way ADO.NET recomends is to utilize the mechanics of a DataTable/Datarow classes.

 

look at this code from the ADO.NET Cookbook for 'Auto Increment without conflict' where it involves defining the Autoincrement seed and step as -1.

the DataTable.AddRow will repopulate the ID column with the actual ID the DB generates.

 

I highly recommend the ADO.NET Cookbook

[Joe Mamma]

sorry. . . didnt attach the code

AutoIncrementWithoutConflictForm.zip