Wednesday, June 4, 2008

Paging IN SQL 2005

This is the SP For Paging IN SQL(2005)

Create PROCEDURE [dbo].[SelectBlogComments]
@Start INT,
@PageLength INT,
@NoOfPage INT OUTPUT
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @TotalRows INT
DECLARE @PageCheck INT

BEGIN
WITH BlogComments
AS

(Select Row_Number() Over(Order by CommentID)AS RowNo, *
From
[dbo].[BlogComments])

SELECT * FROM BlogComments WHERE RowNo>(@Start)*@PageLength And RowNo<=(@Start+1)* @PageLength

Select @TotalRows=COUNT (CommentID) FROM [dbo].[BlogComments]

SET @PageCheck=@TotalRows%@PageLength

IF(@PageCheck=0)

SET @NoOfPage=@TotalRows/@PageLength

ELSE

SET @NoOfPage=@TotalRows/@PageLength+1

END