SQL – Select ranges of records using variables
Posted by Vlad on October 9, 2007
One annoying thing in SQL 2000 was that you couldn’t select TOP @n records using a variable. The options were to construct the SQL query dynamically or (if you wanted to use stored procedures) to use SET ROWCOUNT @n before the select. And of course not to forget to set it back to 0 as soon as you’re done with it.
SQL 2005 solves this problem. You can now use variables in the TOP count:
CREATE PROCEDURE GetLatestAnnouncements
@topCount int
AS
BEGIN
SELECT TOP (@topCount) * FROM Announcements
ORDER BY DateAdded DESC
END
Another handy addition is the Row_Number() function – it basically gives you the sequential number for a row in a result set. You can use it to get “pages” of records just like those you normally need in a paging DataGrid:
CREATE PROCEDURE GetAnnouncementRangeByDate
@startIndex int,
@pageSize int
AS
BEGIN
SELECT Title, [Text], DateAdded FROM
(SELECT
Row_Number() OVER (ORDER BY DateAdded DESC) as RowPosition,
Title, [Text], DateAdded FROM Announcements
) tempselect
WHERE tempselect.rowposition BETWEEN @startIndex AND @startIndex + @pageSize
END
