Stored procedure for top 1 from multiple tables and update 1 table in 1 query

Question

Post reply

Stored procedure for top 1 from multiple tables and update 1 table in 1 query

mcdaniell2000

Old Hand

Points: 385
More actions
January 12, 2023 at 6:18 pm

#4136928
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.
- This topic was modified 1 year, 10 months ago by mcdaniell2000.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

What is the transaction isolation level? Are there any triggers that might affect the data?

There appears to be no need for DISTINCT since you're using TOP 1 (correctly w/ an ORDER BY).

mcdaniell2000 Old Hand Points: 385 More actions · Answer 2

Thanks @ratbak.

I do not know about transaction isolation level. Grateful if you can throw more light.

There is a trigger on table Transfer, that happens on Insert of record.

Thanks

ratbak Ten Centuries Points: 1358 More actions · Answer 3

What does the trigger do?

Does the data show as updated after the transaction has been committed?

See https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server for some info on transaction isolation levels.

Isolation levels control visibility/access to data while it is being changed. If select is really happening within the same transaction as the update, then transaction isolation level probably makes no difference.

mcdaniell2000 Old Hand Points: 385 More actions · Answer 4

The trigger just inserts a copy of data into a backup table that has same structure.

Yes, data is updated after commit.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Maybe something like this?

WITH result
AS (SELECT TOP(1)
           t.Id
    FROM dbo.Transfer t
        JOIN dbo.[User] b
            ON b.Bnumber = t.bnumber
    WHERE t.Stage = 0
          AND RTRIM (LTRIM (t.TransactionNumber)) = @transno
    ORDER BY t.Id)
UPDATE t
SET t.Stage = 1
FROM dbo.Transfer t
    JOIN result r
        ON r.Id = t.Id;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin