Pagination using sql server database OR Server side paging using sql server

Pagination using sql server database
OR
Paging from sql server database
OR
Server side paging using sql server

First i am creating a table for testing purpose:

CREATE TABLE [dbo].[tblCustomers](

[CustomerID] [nchar](5) NOT NULL,

[CompanyName] [nvarchar](40) NOT NULL,

[ContactName] [nvarchar](30) NULL,

[ContactTitle] [nvarchar](30) NULL,

[Address] [nvarchar](60) NULL,

[City] [nvarchar](15) NULL,

[Region] [nvarchar](15) NULL,

[PostalCode] [nvarchar](10) NULL,

[Country] [nvarchar](15) NULL,

[Phone] [nvarchar](24) NULL,

[Fax] [nvarchar](24) NULL,

CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED
(

[CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


Insering records in this tables:

DECLARE @max AS INT, @rc AS INT;

SET @max = 100;

SET @rc = 1;

WHILE @rc <= @max

BEGIN

INSERT INTO tblCustomers

(CustomerID,CompanyName,ContactName,ContactTitle,

Address,City,Region,PostalCode,Country,Phone,Fax)

VALUES

(@rc,'Company'+ CAST(@rc AS VARCHAR),'Name'+CAST(@rc AS VARCHAR),'Title'+CAST(@rc AS VARCHAR),

'Address'+CAST(@rc AS VARCHAR),'Cuty'+CAST(@rc AS VARCHAR),'Region'+CAST(@rc AS VARCHAR),

'pin'+CAST(@rc AS VARCHAR),'Country'+CAST(@rc AS VARCHAR),'Mobile'+CAST(@rc AS VARCHAR),

'FAX'+CAST(@rc AS VARCHAR))

SET @rc = @rc + 1;

END    
Checking out all the rows effected:


SELECT * FROM tblCustomers ORDER BY CAST(CustomerID AS INT) DESC
Creating stored procedures for the pagination:

CREATE PROCEDURE [dbo].[GetCustomersPageWise]

@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER(

ORDER BY [CustomerID] ASC

)AS RowNumber

,[CustomerID]

,[CompanyName]

,[ContactName]

,[City]

,[Country]

,[PostalCode]

,[Phone]

,[Fax]

INTO #Results

FROM [tblCustomers]

DECLARE @RecordCount INT

SELECT @RecordCount = COUNT(*) FROM #Results

SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

PRINT @PageCount

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

END

Executing stored procedures:

For paging 10 records per page 

exec GetCustomersPageWise '1','10',''

For paging 20 records per page

exec GetCustomersPageWise '1','20',''