set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_Paging_RowNumber]
@SqlStmt nvarchar(max),
@PageIndex int = 0,
@PageSize int = 10,
@SqlCountStmt nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*Default Page Number*/
IF @PageIndex < 0 AND @PageSize < 0
BEGIN
/*Execute dynamic query*/
EXEC( '
WITH TempTCE AS (' + @SqlStmt + ')
SELECT *
FROM TempTCE
'
)
END
ELSE
BEGIN
IF @PageIndex < 0
SET @PageIndex = 0
DECLARE @strStartRowNumber varchar(50)
DECLARE @strEndRowNumber varchar(50)
SET @strStartRowNumber = CAST( ((@PageIndex)*@PageSize+1) AS varchar(50))
SET @strEndRowNumber = CAST( (@PageIndex + 1)* @PageSize AS varchar(50))
/*Execute dynamic query*/
EXEC( '
WITH TempTCE AS (' + @SqlStmt + ')
SELECT *
FROM TempTCE
WHERE RowNumber BETWEEN ' + @strStartRowNumber + ' AND '+ @strEndRowNumber + '
'
)
END
EXEC( @SqlCountStmt )
END