I am looking for a solution to the following problem (similar anyway):
There are banks, each banks having a BANK_NBR, and each bank opens bank accounts. When an new account is created, an account number is assigned to it. For a new account, a record is created in a table that has the following unique key: BANK_NBR, ACCOUNT_NBR.
My question relates to the case where several accounts are created at the same time in the same bank. How do I ensure that each one is assign a different number.
I wish I could use an @@IDENTITY field but each bank needs an acount 1, 2, 3, 4...
The solution probably involves using a
SELECT MAX(ACCOUNT_NBR) + 1 from ACCOUNTS
WHERE BANK_NBR = PassedValue
I realize the chances are small that two accounts be created at the same time at the same bank (probably talking about milliseconds) but I need a robust solution.