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 :)