How to Select and Update Top 10 rows Using Stored Proceure at the same time

Question

Hi Team,

I have to Select and Update top 10 rows from a table at the same time using stored procedure.

Thanks
Sushil

Sample Table Query
Create table TestTable (Id int, Payload nvarchar(25),ReqStatus nvarchar(25))

Stored Procedure

ALTER PROCEDURE [dbo].[TestProce]

AS

BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @id int;
SET @id = (select top(1) Id from TestTable where ReqStatus = 'Pending' order by 1 asc);

Select * from [FrameworkStagingDatabase].[dbo].[TestTable]
where Id = @id

update [FrameworkStagingDatabase].[dbo].[TestTable] set [ReqStatus] = 'InProgress' where Id = @id;

END

I have the below stored Procedure, where My unique key is Id.
My SP only works for 1 row , but I need to fetch and update 10 rows.

Thanks

Answer 1

Use a table variable to store the top 10 id, in my example @id

ALTER PROCEDURE [dbo].[TestProce]
AS

BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @id as table(id int);

    INSERT INTO @id (id)
    select top(10) Id 
    from TestTable 
    where ReqStatus = 'Pending' 
    order by Id asc);

    Select * from [FrameworkStagingDatabase].[dbo].[TestTable]
    where Id in (Select id FROM @id)

    update [FrameworkStagingDatabase].[dbo].[TestTable] 
    set [ReqStatus] = 'InProgress' 
    where Id in (Select id FROM @id);

END

Answer 2

Please also try:

ALTER PROCEDURE [dbo].[TestProce]

AS

BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


select top(10) Id 
into #test
from TestTable 
where ReqStatus = 'Pending' 
order by 1 asc


Select * from [dbo].[TestTable]
where Id in (select  id from #test)

update [dbo].[TestTable] 
set [ReqStatus] = 'InProgress' 
where Id in (select  id from #test)

END

execute [dbo].[TestProce]

Answer 3

If you do not need a specific order, then use this query:

update top(10) TestTable
set [ReqStatus] = 'InProgress'
where ReqStatus = 'Pending'