Paging Data Access

Create procedure northwind_orderspaged

(

@ Pageindex int,

@ Pagesize int

)

As

Begin

Declare @ pagelowerbound int

Declare @ pageupperbound int

Declare @ rowstoreturn int

-- First set the rowcount

Set @ rowstoreturn = @ pagesize * (@ pageindex + 1)

Set rowcount @ rowstoreturn

-- Set the page Bounds

Set @ pagelowerbound = @ pagesize * @ pageindex

Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1

-- Create a temp table to store the select results

Create Table # pageindex

(

Indexid int identity (1, 1) not null,

Orderid int

)

-- Insert into the temp table

Insert into # pageindex (orderid)

Select

Orderid

From

Orders

Order

Orderid DESC

-- Return total count

Select count (orderid) from orders

-- Return paged results

Select

O .*

From

Orders O,

# Pageindex

Where

O. orderid = pageindex. orderid and

Pageindex. indexid> @ pagelowerbound and

Pageindex. indexid <@ pageupperbound

Order

Pageindex. indexid

End