DECLARE @Index INT; DECLARE @PageSize INT; SET @Index = 3; SET @PageSize = 5; SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,* FROM Employee) tblEmployee WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize
Here is what the above code is Doing:
1. The first line declares a variable called @Index and sets it to 3.
2. The second line declares a variable called @PageSize and sets it to 5.
3. The third line selects all the columns from the Employee table.
4. The fourth line assigns a row number to each row in the Employee table.
5. The fifth line filters the rows based on the @Index and @PageSize variables.
6. The sixth line returns the rows to the client.