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 @topSELECT 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
selaromdotnet
Latest posts by selaromdotnet (see all)
- Restarting a Specific Azure Web App Instance - January 13, 2021
- Adding Ratings and Reviews to Sitecore Commerce with YotPo - November 13, 2018
- Sourcetree Suddenly Prompting for Github Credentials - February 22, 2018