Top from store procedure

Question

Top from store procedure

niladri.primalink

SSCommitted

Points: 1751
More actions
August 3, 2013 at 7:26 am

#281325

Hi All,
I have a stored procedure named 'Bacs_ValidProcessingDateList' when I execute this procedure it is working fine.
It shows me 2568 records. How Can I use top clause with this?
I want to see top 10 rows only.
as we do in case of table select top 10* from dbo.testDB can we apply such type of things here?
I want to see the top 10 rows from my sp. how will i do that?
Please help!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

niladri.primalink (8/3/2013)
Hi All,
I have a stored procedure named 'Bacs_ValidProcessingDateList' when I execute this procedure it is working fine.
It shows me 2568 records. How Can I use top clause with this?
I want to see top 10 rows only.
as we do in case of table select top 10* from dbo.testDB can we apply such type of things here?
I want to see the top 10 rows from my sp. how will i do that?
Please help!

The best thing to do would be to add an optional parameter to the stored procedure that defaults to the max value for an INT. Then pass in how many rows you want it to return as a TOP. Otherwise, you'll need to use something like OPENROWSET and that becomes a bit of a dynamic SQL pain if you have to pass other parameters as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WolfgangE SSCertifiable Points: 6328 More actions · Answer 2

Like Jeff I recommend you to add an parameter to your stored procedure and get the top x (10 in your case) within the procedure. In SQL 2012 there is a new possibility for this using the ORDER BY clause with the "offfset" and "fetch next" clause. Look at books online here

If you don't want to change the procedure you could create a temporary table. The execute the procedure and save the result in the temp table (using "insert into ... execute ... ").

Then select the top 10 from the temp table.

You can also try to use the SET ROWCOUNT statement, see books online here. If you set a specific value only the defined number of rows is affected by the following statements. I'm not sure if this works when executing a stored procedure but you can try it.

Still, my favourite method would be the first one.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

I prefer using SET ROWCOUNT @X where @X is the number of rows to display since it is compatible with all SQL versions. Using a variable with TOP(X) was added with SQL2005 I think.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Just remember that SET ROWCOUNT is a deprecated feature and, according to the 2012 deprecation list, is scheduled to go away in the next version of SQL Server. That would be 2014. I recommend against building anything new with it.

Here's the 2012 deprecation list.

http://msdn.microsoft.com/en-us/library/ms143729.aspx

Of course, if you choose either of the first two options or the OPENROWSET route, you won't have to worry about SET ROWCOUNT being deprecated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 5

Jeff Moden (8/3/2013)
Just remember that SET ROWCOUNT is a deprecated feature and, according to the 2012 deprecation list, is scheduled to go away in the next version of SQL Server. That would be 2014. I recommend against building anything new with it.
Here's the 2012 deprecation list.
http://msdn.microsoft.com/en-us/library/ms143729.aspx
Of course, if you choose either of the first two options or the OPENROWSET route, you won't have to worry about SET ROWCOUNT being deprecated.

OK, didn't know that. Since it's been around so long it makes sense it would be deprecated. :crazy:

I was also thinking that since it's a 'SET' command that it may not allowed to be used inside a function either. That wasn't part of the original question, but is a downside to that operator.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Of course the other problem with SET ROWCOUNT is that it could affect the outcome in unseen ways, restricting every query within the SP to the limited number of rows, not just the final output.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

WolfgangE SSCertifiable Points: 6328 More actions · Answer 7

That is a very good point to be aware of. I did not think about that.

Naidu PK SSCrazy Points: 2481 More actions · Answer 8

Try use CTE and select top 10 from cte. I did not test this myself but believe it should work.

Thanks,
Naveen.
Every thought is a cause and every condition an effect

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Naveen PK (8/4/2013)
Try use CTE and select top 10 from cte. I did not test this myself but believe it should work.

How would you use the output of a stored procedure from a CTE? I could be wrong but the only way that I can think of doing that would be to us OPENROWSET to call the proc in the CTE and, if you can do that, you don't actually need the CTE. You could SELECT TOP 10 directly from the OPENROWSET.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

Jeff Moden (8/3/2013)
Just remember that SET ROWCOUNT is a deprecated feature and, according to the 2012 deprecation list, is scheduled to go away in the next version of SQL Server. That would be 2014. I recommend against building anything new with it.

Oh, if it only had been that well!

What is deprecated is using SET ROWCOUNT with INSERT, UPDATE, DELETE and MERGE. But not with SELECT. Which I find unfortunate, because SET ROWCOUNT is really horrible. I once ran into panic, because a couple stored procedures failed to fulfil their contracts. Turned out eventually that they were called from an outer procedure where the programmer used SET ROWCOUNT 1 to get rows from his temp table, and then failed to issue SET ROWCOUNT 0 before calling the inner procedure.

In our load tool that we use, I check for SET ROWCOUNT and disallow it across the board.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

Erland Sommarskog (8/4/2013)
Jeff Moden (8/3/2013)
Just remember that SET ROWCOUNT is a deprecated feature and, according to the 2012 deprecation list, is scheduled to go away in the next version of SQL Server. That would be 2014. I recommend against building anything new with it.
Oh, if it only had been that well!
What is deprecated is using SET ROWCOUNT with INSERT, UPDATE, DELETE and MERGE. But not with SELECT. Which I find unfortunate, because SET ROWCOUNT is really horrible. I once ran into panic, because a couple stored procedures failed to fulfil their contracts. Turned out eventually that they were called from an outer procedure where the programmer used SET ROWCOUNT 1 to get rows from his temp table, and then failed to issue SET ROWCOUNT 0 before calling the inner procedure.
In our load tool that we use, I check for SET ROWCOUNT and disallow it across the board.

Crud. You're correct. I missed that part. Thank you for the correction. Heh... leave it to MS. Partial inclusion of new features, partial exclusion of deprecated features. Oh! I know... let's open a CONNECT item on the subject! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niladri.primalink SSCommitted Points: 1751 More actions · Answer 12

Hi All

Thanks for your response....

it is now now working. I have used the below one

SELECT top 1* from OPENQUERY ([localhost],
'EXECUTE [ap_stuartandParker].[dbo].[Bacs_ValidProcessingDateList]' )

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 13

Beware that this is a very bad solution. If were to meet in a code review I would never approve of this solution. Did you read the article I pointed you to?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Stefan_G SSCertifiable Points: 6609 More actions · Answer 14

Stefan_G

SSCertifiable

Points: 6609

August 5, 2013 at 3:07 am

#1638540

Why is this such a bad solution?