Pagination in SQL Server 2008.

In this I will explain Pagination in sql server 2008.

Please follow the steps:-

Open  sql server 2008 Management studio à create a new Database à create a table like this

Create table Details (Id int primary key , Name varchar(50), Country varchar(50));

Now insert the dummy data in the table.

Here I am inserting 1000 data one at a time. See the code given below ..

declare @count int=1;

declare @max int=5000;

delete from Details;

while(@count<=@max)

begin

insert into Details(Id,Name,Country)

select @count ,'Name'+CAST(@count as varchar(5)),'Country'+CAST(@count as varchar(5))

set @count=@count+1

end

This will insert 1000 data in the table.

Now create a stored procedure .

CREATE PROCEDURE PaginationIn2008

(

@start int=1,

@end int=500

)

AS

BEGIN

      select * from Details where Id between @start and @end

      order by Id

END

GO

Execute stored procedure.

To get data from 1 to 10 , execute the procedure like this

exec PaginationIn2008 1,10

If you want 100 to 200 , execute the procedure like this.

exec PaginationIn2008 100,200