Hi Folks,
I stuck in a puzzle, please suggest a way to resolve this.
I have 2 below Tables in my database.
Tab_A (OrderNo, Col1, Col2.......Coln),
Tab_B (OrderHeaderNo, Col1,Col2, Col3........Coln)
Case: At the time of data insertion in both tables, orderNo/OrderHeaderNo should be a max of existing both columns. (New number should not exist in any table's columns ). We have a procedure to insert the data into tables, which is triggered by the application
I created below function to get maximum value from both tables. but if 2 different users create order then the duplicate value has been inserted. I want a solution which can avoid duplicate values.
ALTER FUNCTION [dbo].[GetMaxOrderNo]
(
)
RETURNS int
AS
BEGIN
DECLARE @MaxOrderNo AS INT
SELECT @MaxOrderNo = max(cast(a.OrderNo as int)) from
( SELECT OrderNo as OrderNo from OM_SalesOrderHeader
UNION
SELECT InterBranchOpenOrderNo from InterBranchOpenOrderHeader
) AS a
RETURN @MaxOrderNo
END
Why not use an identity column?. Triggering the max + 1 logic via the application/calling would make the process to follow a queue(ie make the user1 transaction complete in the proc. followed by user 2).
Agreed. Identity in the OrderHeader table seems easiest solution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.