Concurrent INSERTS in stored procedure problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Concurrent INSERTS in stored procedure problem

Author

Topic

mtlmonk
Starting Member

6 Posts

Posted - 2008-03-31 : 15:21:45

Hi,

I have a stored procedure that does insert in a table with a column that is from a generated value (last no found in a text column for that same table) and some other stuff afterwards.

It seems that if two calls to the stored procedures is happening at the same time, the generated value is the same for both since the 1st call did not finish inserting the record yet. Which is wrong obviously.

Ex.:
tblreceipt
receiptid, pk (identity column)
receiptno, varchar(10)
branchid, int

Calls:
exec OrderInsert @branchid = 7, @custid = 1
exec OrderInsert @orderdate = 7, @custid = 1 (almost same time)

Code of Stored Procedure:

CREATE PROCEDURE ReceiptHeaderInsert
@branchid INT,
@custid INT,
@receiptno VARCHAR(10) = NULL
AS
BEGIN

DECLARE @receiptid INT
DECLARE @cnt INT

SET NOCOUNT ON

INSERT INTO tblReceipt
(receiptno)
VALUES
(dbo.uf_GenReceiptNo(@branchid))

IF @@ERROR <> 0
BEGIN
SELECT 0
RETURN
END

SET @receiptid = @@IDENTITY

IF @RECEIPTNO IS NULL
BEGIN
SELECT @receiptno = receiptno FROM TBLRECEIPT WHERE RECEIPTID =@receiptid
END

[...] some other stuff........

SELECT @receiptid 'RECEIPTID', @receiptno 'RECEIPTNO', @modidate 'modidate'

END
GO

Code of UDF used in Stored procedure to generate receiptno
CREATE FUNCTION uf_GenReceiptNo(@branchid INT) RETURNS VARCHAR(30)
AS
BEGIN

DECLARE @branchcode VARCHAR(10)
DECLARE @count INT
DECLARE @rv VARCHAR(30)

SET @branchcode = (SELECT branchcode FROM tblBranch
WHERE branchid = @branchid)

--Gets the last receiptno found
SET @count = (SELECT MAX(CONVERT(INT, SUBSTRING(receiptno, CHARINDEX('-', receiptno) + 1, DATALENGTH(receiptno))))
FROM tblReceipt
WHERE receiptid IN
(SELECT receiptid FROM tblBranchReceipt
WHERE branchid = @branchid))

IF @count IS NULL
SET @count = 0

--Increments by one
SET @count = @count + 1

SET @rv = @branchcode + '-' + CONVERT(varchar(9), @count)
RETURN @rv

END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-31 : 15:34:07

one way to deal with this is with application locks:
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-02 : 02:38:43

This will seriously limit your concurrency though. Best not to do what you are doing at all and use the identity column as your receipt number.
I can tell this will degenerate into a 'but I need it to be gap free' discussion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources