SQL SERVER – Server Side Paging in SQL Server 2005 or 2008

–creating the table.
create table dbo.EmployeeTable
(
EmpId int identity primary key,
EmpName varchar(40),
Sal int
)
go

–inserting data into table
insert into EmployeeTable values(‘ranga’,2000),(‘rajesh’,3000),(‘kumar’,4000),(‘swapna’,5000),(‘gopi’,6000)

–creating view.
create view pagingView
as
select empid,empname,sal from EmployeeTable
go

–creating procedure
CREATE PROCEDURE [dbo].[spPaging]
(
@PageNumber int,
@PageRecordsSize int
)
AS
begin
SET NOCOUNT ON;

SELECT TOP(@PageRecordsSize) * FROM
(
SELECT Row_ID = ROW_NUMBER() OVER (ORDER BY empid),
empid, empname, sal, TotalRows=Count(*) OVER()
FROM pagingView
)
A WHERE A.Row_ID > ((@PageNumber-1)*@PageRecordsSize)
SET NOCOUNT OFF
end
go

exec spPaging 1,5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s