分页存储过程

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