I stuck in a puzzle, please suggest a way to resolve this.
I have 2 below Tables in my database.
, Col1, Col2.......Coln),
, 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]
DECLARE @MaxOrderNo AS INT
SELECT @MaxOrderNo = max(cast(a.OrderNo as int)) from
( SELECT OrderNo as OrderNo from OM_SalesOrderHeader
SELECT InterBranchOpenOrderNo from InterBranchOpenOrderHeader
) AS a