Paging in SQL Server.

declare @intNumOfRecords int, @intPageNum int

Declare @strQuery nvarchar(1000), @intTotal int

set @intNumOfRecords = 50

set @intPageNum = 2

Set @intTotal = @intNumOfRecords * @intPageNum

Set @strQuery = 'Select TOP ' + Cast(@intNumOfRecords as Varchar(5)) + ' * From Sales'

Set @strQuery = @strQuery + ' Where Sno Not in (Select TOP ' + Cast(@intTotal as Varchar(5))

Set @strQuery = @strQuery + ' Sno From Sales Order By Sno ) '

print @strQuery

--Lets execute the dynamically built query

Exec sp_executesql @strQuery

Go

Select TOP 50 * From Sales Where Sno Not in (Select TOP 100 Sno From Sales Order By Sno )

Sno YearOfSales SalesQuarter Amount

----------- ----------- ------------ ---------------------

101 2006 4 1200.00

102 2006 4 1200.00

103 2006 4 1200.00

104 2006 4 1200.00

105 2006 4 1200.00

106 2006 4 1200.00

107 2006 4 1200.00

…

125 2006 4 1200.00

126 2006 4 1200.00