Pagination in SQL Server 2000

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 1246
Let us first set up the environment for performing the demonstration.We will populate 100 records to a tblPagingExperiment table

--Drop the table tblPagingExperiment if it exists
IF OBJECT_ID('tblPagingExperiment','U') IS NOT NULL BEGIN
DROP TABLE tblPagingExperiment

-- Create the table
Create Table tblPagingExperiment (
[Person ID] Int Identity
,[Person Name] Varchar(100)
,Age Int
,DOB Datetime
,Address Varchar(100)

-- Populate 100 data to the table
Insert into tblPagingExperiment
'Person Name' + CAST(Number AS VARCHAR)
, Number
,DATEADD(D,Number, '1900-01-01')
,'Address' + CAST(Number AS VARCHAR)
From master..spt_values
Where Type = 'p'
And Number Between 1 and 100

-- Project the records
Select *
From tblPagingExperiment

Objective: To skip first 10 rows and display next 20 rows.i.e. from row number 11 to 30

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
Set @RowSkip = 10
--Set the value of rows to fetch
Set @RowFetch = 20

--If the #Temp object exists in the tempdb, then drop it
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
Drop Table #Temp

--Create a temporary table
Create Table #Temp
Rn int Identity
,[Person ID] int
,[Person Name] Varchar(50)
,Age int
,DOB datetime
,Address Varchar(100)

-- Insert the records into the Temporary table
Insert Into #Temp([Person ID],[Person Name],Age,DOB,Address)
Select [Person ID],[Person Name],Age,DOB,Address
From tblPagingExperiment

-- Fetch the records from 11 to 30
[Person ID]
,[Person Name]
From #Temp
Where Rn Between (@RowSkip+1) And (@RowSkip+ @RowFetch)

Comments or Responses

Login to post response