Only get top X rows of a stored procedure's results set
-
I have a question, I hope this has not been asked before But I have a stored procedure which I have to EXEC in order to get a results set, and then pop the results set into an Excel report. Problem is, the results set is known to have 100,000's of rows. Now, I've read on Google about SET ROWCOUNT and I did the following as a test...
SET ROWCOUNT 5
EXEC ....
However, doing that gives me nothing back, not the top 5 rows of the results set like I expect. I don't want to put a TOP 5 (or TOP X you get the idea) in my SELECT inside the stored procedure because doing that will break functionality elsewhere. I just want the top X rows right now just for testing purposes, and then when I am ready to deploy I plan to have it give me the full query (the query runs as a scripted console app on a remote server somewhere at regularly scheduled times). Thanks for any inputs!
Sincerely Yours, Brian Hart
-
I have a question, I hope this has not been asked before But I have a stored procedure which I have to EXEC in order to get a results set, and then pop the results set into an Excel report. Problem is, the results set is known to have 100,000's of rows. Now, I've read on Google about SET ROWCOUNT and I did the following as a test...
SET ROWCOUNT 5
EXEC ....
However, doing that gives me nothing back, not the top 5 rows of the results set like I expect. I don't want to put a TOP 5 (or TOP X you get the idea) in my SELECT inside the stored procedure because doing that will break functionality elsewhere. I just want the top X rows right now just for testing purposes, and then when I am ready to deploy I plan to have it give me the full query (the query runs as a scripted console app on a remote server somewhere at regularly scheduled times). Thanks for any inputs!
Sincerely Yours, Brian Hart
Brian C. Hart, Ph.D. wrote:
I don't want to put a TOP 5 (or TOP X you get the idea) in my SELECT inside the stored procedure because doing that will break functionality elsewhere.
Make a second version with a different name?
I are Troll :suss:
-
I have a question, I hope this has not been asked before But I have a stored procedure which I have to EXEC in order to get a results set, and then pop the results set into an Excel report. Problem is, the results set is known to have 100,000's of rows. Now, I've read on Google about SET ROWCOUNT and I did the following as a test...
SET ROWCOUNT 5
EXEC ....
However, doing that gives me nothing back, not the top 5 rows of the results set like I expect. I don't want to put a TOP 5 (or TOP X you get the idea) in my SELECT inside the stored procedure because doing that will break functionality elsewhere. I just want the top X rows right now just for testing purposes, and then when I am ready to deploy I plan to have it give me the full query (the query runs as a scripted console app on a remote server somewhere at regularly scheduled times). Thanks for any inputs!
Sincerely Yours, Brian Hart
How about
create or replace procedure SomeProcedure (
input_select_count integer default null
)
as
...
...
begin
...
if ( input_select_count is NULL )
....
else
....Even better to have the count applied to your SQL as a parameter so that your code can control the number of rows returned. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
How about
create or replace procedure SomeProcedure (
input_select_count integer default null
)
as
...
...
begin
...
if ( input_select_count is NULL )
....
else
....Even better to have the count applied to your SQL as a parameter so that your code can control the number of rows returned. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Thanks for the tip! I'll keep that in mind
Sincerely Yours, Brian Hart
-
Brian C. Hart, Ph.D. wrote:
I don't want to put a TOP 5 (or TOP X you get the idea) in my SELECT inside the stored procedure because doing that will break functionality elsewhere.
Make a second version with a different name?
I are Troll :suss:
I did that and it worked! Thanks [:)]
Sincerely Yours, Brian Hart
-
I did that and it worked! Thanks [:)]
Sincerely Yours, Brian Hart
