Getting Top N from stored procedure

Question

I have a stored procedure that cannot be modified, I'm going to stress this before anyone suggests I re-write the stored procedure or add the query from inside the stored procedure into a function.

The procedure lives on another database that we have very limited access to; so what I want to do is somehow wrap the stored procedure in a query, function or stored procedure that will allow me to select the top N rows from the returned data.

Ideally I would be able to call something like...

DECLARE @ForeName varchar(50)
DECLARE @Surname varchar(50)
DECLARE @DOB datetime
DECLARE @Sex varchar(1)

SET @Surname = 'Smith'

SELECT TOP 10 (
   EXECUTE @RC = [Some_Other_Database].[dbo].[sp_search_demographics] 
     ,@ForeName
     ,@Surname
     ,@DOB
     ,@Sex
)
GO

edit: (I should also note that the stored procedure returns a parameter containing the row count as well as the rows)

edit2: I should also note that I'm using MS SQL Server 2008 R2

I'm aware that this is in no way correct, is there any way to do something like this? at the moment for vague queries we are getting thousands of rows returned; which is slowing the server considerably.

I have done some Googling and stack-overflowing for a solution but unfortunately all the advice I could find involved modifying the stored procedure.

The only way you can do this without rewriting that *remote* procedure is to put the output from that proc into a temporary table and select from there. But that means : retrieving the whole output and then selecting only the top 10 rows from it... — marc_s, Jul 13 '12 at 13:26
This boils down to this question essentially: [SQL Server - SELECT FROM stored procedure](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure). — Andriy M, Jul 13 '12 at 13:27
@AndriyM: yes, of course - but it basically does the same thing, meaning: even that approach cannot limit the output of the remote procedure to just 10 rows, right? — marc_s, Jul 13 '12 at 13:30
@marc_s: I don't really know, you might be right there. I would still expect the overhead of the OPENQUERY method to be less on average, although that, again, is more of a gut feeling than confidence. — Andriy M, Jul 13 '12 at 13:35
What do you mean by *SQL Server 2010*? For there's no such thing. There are SQL Server 2008, SQL Server 2008 R2, SQL Server 2012. Which one is yours? — Andriy M, Jul 13 '12 at 13:37
Whops, 2008 R2* I was looking at the management studio version number (10.0.5500.0) — Waltzy, Jul 13 '12 at 13:44
Dependant upon the logic in the stored procedure `SET ROWCOUNT 10;EXEC your_proc` might work. — Martin Smith, Jul 13 '12 at 13:44
@MartinSmith, that's perfect, put it as an answer and I will accept. — Waltzy, Jul 13 '12 at 13:46
@Waltzy - You would need to look at the definition of the stored proc to check that this is safe though as it will apply to all statements in the proc which might change the semantics (e.g. if it does any intermediate population of table variables or temp tables these statements will be affected too). If the proc is just a simple `SELECT ... WHERE ... ORDER BY` then it should be fine though. — Martin Smith, Jul 13 '12 at 13:50
@MartinSmith, Ok, I't seems to be behaving like I would expect; I'll fire off and email to the owner later but this looks like its working. — Waltzy, Jul 13 '12 at 13:54
@Waltzy: You should still keep in mind what Martin said, in case the procedure changes later to something more complex and involving more than one select. — Andriy M, Jul 13 '12 at 14:37
There are two ways I know. Refer this http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx — Madhivanan, Jul 13 '12 at 13:28

score 3 · Accepted Answer · answered Jul 13 '12 at 13:31

Look up EXEC SP_EXECUTESQL(@SQL) However the problem will be that the called sp will still return all the rows, so you may not get the improvement in performance you are looking for. You can also set the number of rows returned by a query - but depends on your access level http://blog.sqlauthority.com/2007/04/30/sql-server-set-rowcount-retrieving-or-limiting-the-first-n-records-from-a-sql-query/ Hope this helps

score 0 · Answer 2 · edited Jan 27 '15 at 06:02

Declare @i  Numeric(18,2)
Declare @strSQL nvarchar(1000)
select @i = Round(COUNT(1)/10,2) from tb_Item
print(@i)
Declare @j int = 0

Declare @rem numeric(18,2)
select @rem = COUNT(1) - ((COUNT(1)/10) * 10) from tb_Item 

while @i > 0
Begin
    set @j = (@j + 1);

    if @j = 1
    Begin
        WITH OrderedOrders AS
        (
            select 
                ROW_NUMBER() over(order by ItemID) AS RowNumber
                ,ItemName
            from tb_Item 
        ) 
        SELECT ItemName, RowNumber  
        FROM OrderedOrders 
        WHERE RowNumber BETWEEN (@j*10)-10 AND @j*10;
    End
    Else
    Begin
        WITH OrderedOrders AS
        (
            select 
                ROW_NUMBER() over(order by ItemID) AS RowNumber
                ,ItemName
            from tb_Item 
        ) 
        SELECT ItemName, RowNumber  
        FROM OrderedOrders 
        WHERE RowNumber BETWEEN ((@j*10)-10) + 1 AND @j*10;
    End 
    set @i = @i - 1;
end;

WITH OrderedOrders AS
(
    select 
        ROW_NUMBER() over(order by ItemID) AS RowNumber
        ,ItemName  
    from tb_Item 
) 
SELECT ItemName, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN (@j*10)+1 and (@j*10) + @rem ;

Getting Top N from stored procedure

2 Answers2