sqlserver 2000 sp behaviour is strange

[spaliwal]

Hi,

For next no. generation i am using sp in sql server. sometimes it returning same no again , and voilating PK constraints while insertion in application.Below is the code,
i am using default autocommit behaviour of sqlserver2000.
Here Location.LastDeliveryTicketNo is treated as next no.


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE GetNextDeliveryTicketNo (@x_LocationOID varchar(32), @x_NextNo varchar(10) output) AS
DECLARE @x_LastNo varchar(10)
DECLARE @x_StartNo varchar(10)

SELECT @x_LastNo = isnull (LastDeliveryTicketNo, 0)
FROM Location
WHERE OID = @x_LocationOID


IF @x_LastNo > 0
SET @x_NextNo = @x_LastNo
ELSE
BEGIN
SET @x_NextNo = 1
SET @x_LastNo = 1
END

SET @x_LastNo = @x_LastNo + 1


UPDATE Location
SET LastDeliveryTicketNo = @x_LastNo
WHERE OID = @x_LocationOID

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Pl. suggest.

Thanks,

Sheilesh