Mystery Stored Procedure Failure

[tcorrigan]

I use the following stored procedure to assign the next invoice number in my applcation:

CREATE PROCEDURE InvoiceNewInvoice
@nextinv int output
AS
set nocount on
while 1=1 begin
   select
      @nextinv = nextinv
   from
      system
   where
      pkey = 1

   update
      system
   set
      nextinv = nextinv + 1
   where
      pkey = 1 and
      nextinv = @nextinv

   if @@rowcount = 1
      break
end

This procedure has been used for months without incident but today it skipped 5 numbers in a row, i.e., it skipped from 2545457 to 2545463. At the time it happened the system was "running slow" but only one person was entering invoices. Anyone have any ideas as to why this would happen? Also a critique of the procedure used as well as suggested alternatives to handing out sequential numbers without gaps would be appreciated.

 

[tlbroadbent]

Try this procedure instead. It is simpler and should be less prone to problems.

CREATE PROCEDURE InvoiceNewInvoice
@nextinv int output
AS
set nocount on
while 1=1
begin
update
system
set
nextinv = nextinv + 1,
@nextinv = nextinv + 1
where
pkey = 1
end Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[tcorrigan]

Hi Terry,

Thanks for your response. I do have some questions, however. As written it appears that your procedure would remain in an infinite loop (while 1=1 ... end). My take on the subject is that you want to keep the loop but use a check for @rowcount = 1 as the exit condition, but maybe you're trying to tell me that single update by itself suffices.

My concern is that if you have two users executing this procedure simultaneously you have to make sure that you don't hand out the same number twice. My understanding is that single SQL statements are atomic, i.e., SQL Server guarantees that it's not possible for two users to execute the update simultaneously, one of them always has to wait for the other one to finish the update completely and hence there's no possibility of handing out the same number twice. If that's true then the single update without the loop should do the job. Your comments please.

Tom

 

[tlbroadbent]

Whoops! I'd remove the loop. Sorry about that.

CREATE PROCEDURE InvoiceNewInvoice
@nextinv int output
AS
set nocount on
update system
set nextinv = nextinv + 1,
@nextinv = nextinv + 1
where pkey = 1 Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[tcorrigan]

Thanks for the response.

Tom