Getting a stored procedure to run only one instance at a time...

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
USE [PS_Brandon_Dev01]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_nextnum]
      -- Add the parameters for the stored procedure here
      @Method nvarchar(50),
      @NextNum bigint Output
            
AS
BEGIN
      SET NOCOUNT ON;
      update seed set nextnum = nextnum + 1 where method = @Method
      select @NextNum = nextnum from seed where method = @Method
END

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.

Thanks!
Bran-DamageAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Just use this

ALTER PROCEDURE [dbo].[sp_nextnum]
      -- Add the parameters for the stored procedure here
      @Method nvarchar(50),
      @NextNum bigint Output
           
AS
BEGIN
      SET NOCOUNT ON;
      update seed set @NextNum = nextnum = nextnum + 1 where method = @Method
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bran-DamageAuthor Commented:
This looks like it could work.  Since now everything will be done in the same transaction it should only run one at a time.  Perfect.  Thanks man!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.