CREATE PROCEDURE [dbo].[proc_SplitPageSearch]
@SqlStr NVARCHAR(MAX),
@OrderStr NVARCHAR(MAX),
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUTPUT
AS
BEGIN
IF @PageIndex IS NULL SET @PageIndex=1
IF @PageSize IS NULL SET @PageSize=20
DECLARE @Sql NVARCHAR(MAX)
SET @Sql='SELECT @RecordCount=COUNT(*) FROM ('+@SqlStr+') ps1'
EXEC sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SET @Sql='SELECT * FROM (
SELECT TOP ' + CONVERT(VARCHAR,@PageIndex*@PageSize) + ' ROW_NUMBER() OVER (ORDER BY '+@OrderStr+') AS ROWID,ps1.*
FROM (' + @SqlStr + ') ps1
) ps2 WHERE ps2.ROWID>'+CONVERT(VARCHAR,(@PageIndex-1)*@PageSize)
EXEC(@Sql)
END
@SqlStr NVARCHAR(MAX),
@OrderStr NVARCHAR(MAX),
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUTPUT
AS
BEGIN
IF @PageIndex IS NULL SET @PageIndex=1
IF @PageSize IS NULL SET @PageSize=20
DECLARE @Sql NVARCHAR(MAX)
SET @Sql='SELECT @RecordCount=COUNT(*) FROM ('+@SqlStr+') ps1'
EXEC sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SET @Sql='SELECT * FROM (
SELECT TOP ' + CONVERT(VARCHAR,@PageIndex*@PageSize) + ' ROW_NUMBER() OVER (ORDER BY '+@OrderStr+') AS ROWID,ps1.*
FROM (' + @SqlStr + ') ps1
) ps2 WHERE ps2.ROWID>'+CONVERT(VARCHAR,(@PageIndex-1)*@PageSize)
EXEC(@Sql)
END