Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara