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