Select Query: Retrieve Fixed Number of Rows
As I'm beginning work on my senior project, I have discovered the need to select a specific number of rows from a SELECT query in SQL Server 2000. Although the new 2005 version has the built in ability to pass a variable for the TOP clause, no such functionality exists in SQL SERVER 2000.
Fortunately I found a great resource showing various ways to accomplish it. The best one in my opinion is to pass the variable into the SET ROWCOUNT command, which can take a variable as a value:
CREATE PROCEDURE dbo.getFoo
@top INT
AS
BEGIN
SET ROWCOUNT @top
SELECT foo
FROM blat
ORDER BY foo DESC
-- never forget to set it back to 0!
SET ROWCOUNT 0
END
GO
This preserves the security your stored procedure (vs building it as a string) and keeps things relatively tidy.
It's a shame it took them this long to add a built-in mechanism into SQL SERVER but I'm just glad there's always a workaround :)
Enjoyed this post and/or found it useful?
Tagged with:
SelAromDotNet
Josh loves all things Microsoft and Windows, and develops solutions for Web, Desktop and Mobile using the .NET Framework, Azure, UWP and everything else in the Microsoft Stack.
His other passion is music, and in his spare time Josh spins and produces electronic music under the name DJ SelArom.
