I have two tables in this format below;
Id Stage Amount TransactionNumber bnumber
Id Bnumber FirstName FirstLastName
My aim is to select top 1 from these tables based on criteria and then update that record in the Transfer table, all in one query. I have struggled and came up with the procedure below;
CREATE PROCEDURE [dbo].[Test] @transno nvarchar(50) as Begin transaction declare @id int declare @stage int begin SELECT distinct top 1 @id = t.Id, @stage = t.Stage FROM dbo.[Transfer] t inner JOIN dbo.[User] b on b.Bnumber = t.bnumber where t.Stage = 0 and RTRIM(LTRIM(t.TransactionNumber)) = @transno order by t.Id end update dbo.[Transfer] set Stage = 1 where Id = @id SELECT distinct top 1 t.Id,ROUND(t.Amount,2,1) as 'Amount',b.FirstName + ' ' + b.FirstLastName UserName,t.Stage FROM dbo.[Transfer] t inner JOIN dbo.[User] b on b.Bnumber = t.bnumber where t.id=@id order by t.Id commit
The challenge is I have noticed that multiple calls to this procedure produce the same result sometimes, especially if these calls are within seconds/microseconds apart.
Any help in optimizing this will be appreciated.