CODEWITHSUNDEEP
sql-serverstored-procedures
Vinod Kumar
Vinod Kumar

Reputation: 408

Get first N records from stored procedure result set in SQL Server

Is there is any solution / query to get first N records from stored procedure result without retrieving the whole result set?

Consider my stored procedure returns 3 million rows, and I just want the first 10 rows from it.

Upvotes: 1

Views: 5036

Answers (3)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

You can use TOP clause to achieve it

Syntax

SELECT TOP number|percent column_name(s) 
FROM table_name 
WHERE condition;

Let's say that you have Your_stored_procedure return list of users

CREATE PROCEDURE Your_stored_procedure
AS
    SELECT UserId, UserName
    FROM yourtable
GO;

At here, you need to create temp table to store value from stored procedure

-- Check result
CREATE TABLE #TempTable 
(
    UserId          INT,
    UserName        varchar(100),
)
INSERT INTO #TempTable(UserId, UserName) 
EXEC Your_stored_procedure

Then you can get the result like this way

SELECT TOP 10 UserId, UserName
FROM #TempTable 
ORDER BY UserId -- As @Squirrel's comment, TOP should come with ORDER BY

Note

You should make sure that the number of columns in your table according to the structure of the stored procedure.

Updated

As @Vinod Kumar's comment, you can also achieve it by using OPENQUERY like below

SELECT top 1 * FROM OPENQUERY ([MyServer], 'EXEC [VinodTest].[dbo].[tblAuthorsLarge] year = 2014')

Upvotes: 1

Martin Cairney
Martin Cairney

Reputation: 1767

The best approach would be to alter your stored procedure to be able to include a parameter for the TOP filter.

However, you could also use 

SET ROWCOUNT 10
EXEC MyProc

Be careful to reset the value of ROWCOUNT afterwards otherwise you may impact other queries.

The downside is that you cannot control the order of the rows. I also haven't tested with such a large result set to identify whether this does reduce resource consumption enough.

Upvotes: 2

Prajakta Kale
Prajakta Kale

Reputation: 391

You can use Fetch next clause. Please refer this for more information.

 SELECT column-names
  FROM table-name
 ORDER BY column-names
OFFSET n ROWS
 FETCH NEXT m ROWS ONLY

Upvotes: 0

Related Questions