DataList Paging Store Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_GetCategoryProduct]

@CategoryId int,
@PageIndex int,
@RowsPerPage int,
@RowCount int OutPut,
@PageCount int Output,
@PageInfo varchar(250) Output

AS

Declare @StartRowIndex int;
Set @StartRowIndex = (@PageIndex * @RowsPerpage) + 1;

DECLARE @TempItems TABLE
(
ID int IDENTITY,
ProdId int
)

INSERT INTO @TempItems (ProdId)
SELECT Product_Master.ProdId
FROM Product_Master inner join companycategory
on product_master.prodcateid=companycategory.prodcateid
where categoryid=@Categoryid

Select @RowCount = count(*) from @TempItems;
Set @PageCount = @RowCount / @RowsPerPage;

if @PageIndex > @PageCount
raiserror ('Out of Page Index', 16,1) ;


Select @PageInfo =
'Showing ' +
ltrim(str(@startRowIndex)) + ' - ' +
case
when (@RowCount > (@startRowIndex + @RowsPerPage) - 1)
then ltrim(str(@startRowIndex + @RowsPerPage) - 1)
else
ltrim(str(@RowCount))
end
+ ' of ' +
ltrim(str(@RowCount));


Select *

FROM @TempItems t
Inner Join Product_Master ON Product_Master.ProdId = t.ProdId
Where t.ID between @startRowIndex AND (@startRowIndex + @RowsPerPage) - 1;