1

I have a scenario whereby I need to retrieve an INT from a database table, increment it by one then return the old value. I need to ensure that if the stored procedure is called again immediately then the returned value (NextBookingId) is not a identical to the value returned by the first call of the stored prodcedure.

I've been using the code below and assumed it was working but I don't think it's been properly tested until now and it appears not to be working, as 2 seperate and immediate calls of the stored procedure appear to be returning the same value.

DECLARE @NextBookingId INT

UPDATE Company WITH (ROWLOCK)
SET  @NextBookingId = (NextBookingId),
     NextBookingId = (NextBookingId + 1)
WHERE CompanyId = @CompanyId 

SELECT 
    CompanyId
,   @NextBookingId AS NextBookingId
FROM Company
WHERE CompanyId = @CompanyId

Should this be wrapped in a transaction? I have a clustered index on the CompanyId primary key.

ledragon
  • 299
  • 6
  • 17
  • Maybe OUTPUT DELETED or INSERTED would be useful?https://msdn.microsoft.com/en-us/library/ms177564.aspx – Liesel Jul 06 '16 at 11:01
  • Don't decide your next booking number in advance. Instead, find it at the time of inserting next booking into the system. Wrapping the insert query in a transaction block would help as well. – Kaf Jul 06 '16 at 11:07

0 Answers0