I need to have a unique number every time for an identifier. The database did not have an identity column for the value they needed to be unique so what they did is something like this...
i = Select max(unitid) from table
i = i + 1
insert into table values (i, abc, abc, abc.....)
Now, this is an enterprise level application so this does not work. Because the select could be run at the same time as somebody across the world and they both inserted the same value into the table.
So what I did to try and rectify this issue is wrote this stored procedure
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_nextnum]
-- Add the parameters for the stored procedure here
@NextNum bigint Output
SET NOCOUNT ON;
update seed set nextnum = nextnum + 1 where method = @Method
select @NextNum = nextnum from seed where method = @Method
Everything was going great until today... one of my high volume customers ended up having 3 records with the same unitid.
It was my understanding that stored procedures get queued and run one at a time, but I must have been wrong.
Is there a way to lock a stored procedure and unlock it per request so that the others get queued until the stored procedure is finished?
I should mention this issue occurred on a SQL2000 database. Dont know if that is any correlation but thought i would throw it out there.
I am also calling this stored procedure through c# using the sqlClient.