Cybercrux

Everything is achievable through technology

procedure paging

Pagination in SQL A Light weight approach

Demo Code
Create a Table

CREATE TABLE [dbo].[Personal](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](250) NULL
) ON [PRIMARY]

Create Procedure

— =============================================
— Author: Nikhil Sreeni
— Create date: 16-Mar-12
— Description: Demo Procedure for Sql Pagination
— =============================================

CREATE procedure [dbo].[personal_Proc] (
@option nvarchar(20)
,@id int
,@name nvarchar(50)
,@address nvarchar(250)
,@CurrentPage INT
,@PageSize INT
,@SortExp VARCHAR(25)
,@SortDirection VARCHAR(5)
)
as
begin
--Paging Varialbles
DECLARE @FirstRec INT
DECLARE @LastRec INT

SET @FirstRec = (@CurrentPage - 1) * @PageSize
SET @LastRec = (@CurrentPage * @PageSize + 1)

if(@option ='paged')
begin
Declare @TempPersonal TABLE (
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](250) NULL
)

INSERT INTO @TempPersonal ([name],[address])
select [name],[address] from Personal

select * from @TempPersonal WHERE id > @FirstRec And id < @LastRec
select ceiling(cast(Count(id) as float)/@PageSize) TotalPages From @TempPersonal
Select Count(id) TotalRows From @TempPersonal
end

if(@option ='Select')
begin
select * from dbo.Personal
end
end

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