存储过程分页

--[usp_tb_Sort] 10,2,'createdate desc',' select * FROM tb_ItemInspection'


create PROC [dbo].[usp_tb_Sort]
@PageSize Int = 0 --一页显示条数
,@CurrentPageIndex Int = 0 ---显示的页数
,@SortExpression nvarchar(100) ='' ---排序条件
,@SQL VARCHAR(max) --传递过来的SQL语句

AS

CREATE TABLE #RecordCount
(
RecordCount VARCHAR(50)
)
BEGIN

DECLARE @TempSql varchar(MAX) --临时存放sql语句
DECLARE @ListSql varchar(MAX) --临时存放sql语句
DECLARE @RecordCount VARCHAR(50)

set @TempSql='SELECT COUNT(1) FROM ('+@SQL+') t'
insert into #RecordCount EXEC (@TempSql)
SET @RecordCount= (SELECT RecordCount FROM #RecordCount)

Declare @StartRow VARCHAR(50)
Declare @EndRow VARCHAR(50)
Set @StartRow = (@CurrentPageIndex-1)*@PageSize+1
Set @EndRow = @CurrentPageIndex*@PageSize

IF(@CurrentPageIndex>1 AND @StartRow>@RecordCount)
BEGIN
SET @CurrentPageIndex=@CurrentPageIndex-1
SET @StartRow = (@CurrentPageIndex-1)*@PageSize+1
SET @EndRow = @CurrentPageIndex*@PageSize
END

SELECT RecordCount FROM #RecordCount

set @ListSql ='SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (
Order By '+ @SortExpression + '
) AS RowNum
,*
FROM ('+ @SQL+') t
) AS RECORDS
WHERE RowNum BETWEEN '+ @StartRow +'AND '+@EndRow

EXEC(@ListSql)

END