October 28, 2003 at 11:11 am
Hello,
is it possible to specify a number of rows to return from a stored procedure?
Say i order my rows by column A and I want the top 10 of those returned.
Thanks for any help in advance.
October 28, 2003 at 11:15 am
In SQL Server 7.0 and higher the TOP operator is available. For instance:
SELECT TOP 10 OrderID
FROM dbo.Orders
ORDER BY OrderDate
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 28, 2003 at 12:10 pm
What about "SET ROWCOUNT" command?
October 28, 2003 at 12:21 pm
SELECT TOP 10 OrderID
FROM dbo.Orders
ORDER BY OrderDate
Does exactly what i wanted! Thanks a lot.
October 28, 2003 at 12:43 pm
SET ROWCOUNT works, just Microsoft's recommendation is to use TOP.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 29, 2003 at 2:30 am
If you want to define the number of returned rows as a parameter the only possibility is ROWCOUNT like this example:
create proc S_Sample @col1 int, @numrows int
as
set rowcount @numrows
select * from MyTable
where col1 = @col1
order by col2 desc
go
So you will return only the first @numrows rows.
Bye
Gabor
Bye
Gabor
October 29, 2003 at 2:39 am
TOP works but I did come across a curious behaviour the other day. A fairly simple query without TOP ran in a few seconds. With TOP, I killed it after several minutes! This was reproducible and the only difference was replacing 'SELECT <selectlist> ...' with 'SELECT TOP 10 <selectlist> ...'. On checking, the execution plans were entirely different. If anybody can explain this, I would be fascinated.
October 29, 2003 at 4:40 am
Set rowcount has it's uses. One is to easily limit the results of a delete or update, the other is when you need to parameterize it. You can do set rowcount @RecCount, but not select top @Reccount.
Andy
October 29, 2003 at 6:26 am
quote:
... but not select top @Reccount ...
... unless you use dynamic SQL and sp_executesql (to make your plans reuseable)!
best regards,
chris.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply