Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Custom Paging in SQLServer2005 and Dotnet Application

July 8 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Custom Paging in SQLServer2005 and Dotnet Application

Subsets via SQL Server 2005

There are various techniques for handling this issue, but SQL Server 2005 introduced a new feature that changed everything. SQL Server 2005 simplifies retrieving subsets of a data source via the T-SQL row_number function.

The results of applying the row_number function is enclosed in a subquery that returns a temporary named result set. The data you need is picked from this temporary result set.


CREATE TABLE EMPLOYEE(
Emp_ID int IDENTITY(1,1) NOT NULL,
First_Name varchar(250),
Last_Name varchar(250),
Emp_Password nvarchar(250),
Emp_sal decimal(18,2))


T-SQL returns a set of five records from the employee table using the row_number function. The temporary table is named as "e" within the subquery. The row_number function numbers the rows in the subquery according to the ORDER BY clause.

Declare two variables for Page Index and Page Size.
declare @pv_pageIndex int
declare @pv_pageSize int

Set the values for the declared variables

set @pv_pageIndex = 2
set @pv_pageSize = 5

select * from
(select row_number() over(order by emp_sal) AS [rowC], * from employee) e
where e.rowC between (((@pv_pageIndex - 1) * @pv_pageSize) + 1 ) AND (@pv_pageIndex * @pv_pageSize)

Above query returns second 5 records, The rows are numbered by their location in the ORDER BY clause in the subquery that orders the records by Emp_sal.


-----



Share this post

Repost 0

Comment on this post