question

sushilauradkar avatar image
0 Votes"
sushilauradkar asked sushilauradkar commented

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

Hi Team,

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

Thanks
Sushil

103323-image.png




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


sql-server-generalsql-server-transact-sql
image.png (77.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered sushilauradkar commented

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
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your inputs.
When I execute this query , there is syntax error due to use of order by clause

Incorrect syntax near the keyword 'order'.

0 Votes 0 ·

Then remove the ORDER BY clause, even if I can't imagine why it errors.
Which SQL Server version are you using?

0 Votes 0 ·

INCORRECT SYNTAX NEAR ORDER. Expecting ')', EXCEPT or UNION,

i am Using SQL SERVER 2014



0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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]
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.