I have two tables in this format below;
Table Transfer
Id Stage Amount TransactionNumber bnumber
Table User
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.