george-178499 - Saturday, February 10, 2018 6:28 AM

anujkumar.mca - Saturday, February 10, 2018 5:44 AM

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).