August 3, 2013 at 7:26 am
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!
August 3, 2013 at 9:23 am
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
August 3, 2013 at 12:47 pm
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.
August 3, 2013 at 6:26 pm
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.
August 3, 2013 at 6:33 pm
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
August 3, 2013 at 6:44 pm
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.
August 3, 2013 at 6:48 pm
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);
August 3, 2013 at 10:23 pm
That is a very good point to be aware of. I did not think about that.
August 4, 2013 at 6:34 am
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
August 4, 2013 at 8:11 am
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
August 4, 2013 at 4:06 pm
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]
August 4, 2013 at 5:28 pm
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
August 5, 2013 at 2:33 am
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]' )
August 5, 2013 at 2:40 am
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]
August 5, 2013 at 3:07 am
Why is this such a bad solution?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply