Re: Table Locking

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Dickey (anonymous_at_discussions.microsoft.com)
Date: 05/03/04 

Date: Mon, 3 May 2004 13:41:10 -0700

>-----Original Message-----
>John Dickey wrote:
>
>> I think I need some good references to SQL Server to
learn
>> some basic functions.
>>
>> I have a stored procedure that creates document numbers
>> with a value within a table.
>>
>> 2 instances of our application called my stored
procedure
>> and at the same time, they accessed the value and
>> generated duplicate document numbers.
>>
>> Does anyone know if there is a built in, start to
finish,
>> template that I can adopt to safely use a lock in my
>> stored procedure? I would really appreciate some good
SQL
>> Server references as well.
>>
>> John
>
>Perhaps you could post some additional information along
with your
>stored procedure. Using a transaction around the
statements that get
>your document number *should* be enough but without
seeing your code it
>is hard to be sure.
>
>Zach
>.
>

OK, please keep in mind that my stored procedures are
nested several layers so modifying this stored procedure
means modifying other stored procedure which I want to
avoid.

My stored procedure is called spGenerateDocumentNumber
which needs the primary key of TableA and an order number
for parameters.

It will get a sequence number from the table "DocTable"
for the given order number, build a document number using
the sequence number and updates TableA with the document
number.

Afterwards, I update the sequence number

WHILE exists (SELECT * FROM TableA WHERE PriKey =
@tablePriKey)
  BEGIN
    
    EXEC spGenerateDocumentNumber @tablePrikey, @orderNo
    
    UPDATE DocTable
    SET SequenceNumber = SequenceNumber + 1

    SET @tablePriKey = @tablePriKey + 1
    
  END

Quantcast