CODEWITHSUNDEEP
t-sqlstored-proceduressql-server-2008-r2subquerysql-function
Waltzy
Waltzy

Reputation: 1107

Getting Top N from stored procedure

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.

Upvotes: 2

Views: 9255

Answers (2)

harshal sonawane
harshal sonawane

Reputation: 1

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 ;

Upvotes: 0

Ian P
Ian P

Reputation: 1724

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

Upvotes: 3

Related Questions