SQL Header Detail Last Number Problem (overlap identity saving Details)

Hotaxion

Scenario: one User1 press the save gets the last number 1000012 on the header then Run SP for Details saving(with 3 detail line items) at the same time user2 press the save gets 1000013 on the header then Run SP for Details saving (with 1 detail line items) after checking after checking users inserted user1 has 4 lines user2 has 0 lines

ALTER PROCEDURE [dbo].[sp_HeaderSave] (@Lastnumber Decimal) AS

BEGIN

INSERT INTO Header (DOCNo, VersionNo, NoteDate)
VALUES (@Lastnumber,'VersionNo',GETDATE())

END

-- this for the Sp Details

ALTER PROCEDURE [dbo].[sp_DetailSave] (@Lastnumber Decimal) AS

BEGIN

INSERT INTO Detail(DOCNo, ItemNo, Qty)
VALUES (@Lastnumber,'Items One',12)

END

thank you,

Richard Deeming

Don't do it like that. As you've discovered, if two users hit "save" at the same time, they'll both get the same "last number", and overwrite each other's data. Either use an IDENTITY column: IDENTITY (Property) (Transact-SQL) | Microsoft Docs[^] SCOPE_IDENTITY (Transact-SQL) | Microsoft Docs[^] or a SEQUENCE: Sequence Numbers | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Hotaxion

i tired this, how can i pass the @lastnumber it came from other table? :(

CREATE TABLE ControlNumbers (
LastNumber Varchar(50)
)

Victor Nijegorodov

Hotaxion wrote:

CREATE TABLE ControlNumbers ( LastNumber Varchar(50) )

Interesting! In your OP you declared LastNumber as "Decimal". Now you show some other LastNumber which is in this case Varchar(50). Are these two different? Or which type is correct? And why not use the int with Identity? Something like:

CREATE TABLE ControlNumbers (
LastNumber Int IDENTITY(1,1)
)

Richard Deeming

As I said, either use an IDENTITY column, or use a SEQUENCE. A varchar column can't automatically generate new values.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Hotaxion

the front end of the application is returning last number and saving it like this "M01-00002" on the Table i get the Right(lastnumber,5) then Plus 1 :( ty

Hotaxion

the scenario is i can't modified/change the Table datatype coz other application is using it.. lets say the table is on our ERP.

CHill60

Another technique is to have another table that just generates the ids for you

CREATE TABLE IdsList(id INT IDENTITY(1,1), datum CHAR(1));
-- datum is an arbitrary column

In your (single) stored procedure first do this

INSERT #IdsList(datum) VALUES('x');
declare @lastnum int = (SELECT SCOPE_IDENTITY())

I would also suggest putting your stuff into a single SP and wrapping it all up in a transaction

ALTER PROCEDURE [dbo].[sp_HeaderSave] (@Lastnumber int OUTPUT) AS

BEGIN

BEGIN TRANSACTION [MyTrans]

BEGIN TRY

INSERT #IdsList(datum) VALUES('x');
SET @lastnumber int  = (SELECT SCOPE\_IDENTITY())

INSERT INTO Header (DOCNo, VersionNo, NoteDate)
VALUES (@Lastnumber,'VersionNo',GETDATE())

COMMIT TRANSACTION \[MyTran\]

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION \[MyTran\]

END CATCH
END

Richard Deeming

AKA the poor man's SEQUENCE[^]. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer