Select Query: Retrieve Fixed Number of Rows

By in
No comments

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 🙂

-SelArom

The following two tabs change content below.

selaromdotnet

Senior Developer at iD Tech
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. His other passion is music, and in his spare time Josh spins and produces electronic music under the name DJ SelArom.