CTE通用表达式分页

CREATE PROCEDURE [dbo].[spc_Pagination]
	@szTableName NVARCHAR(4000),
	@szField NVARCHAR(4000),
	@szWhere NVARCHAR(4000),
	@szOrder NVARCHAR(4000),
	@PageSize INT,
	@PageIndex INT
AS
DECLARE @szSQL NVARCHAR(4000)

--SET @PageSize = 3
--SET @PageIndex = 3
--SET @szTableName = 'Person'
--SET @szField = 'FirstName, Age, Age2, Gender'
--SET @szOrder = 'Age, Age2 DESC'
--SET @szWhere = 'Age > 6'

SET @szSQL = ''
SET @szSQL = @szSQL + 'DECLARE @total_pages AS INT;' + CHAR(13)
SET @szSQL = @szSQL + 'DECLARE @current_page AS INT;' + CHAR(13)
SET @szSQL = @szSQL + 'DECLARE @page_size AS INT;' + CHAR(13)
SET @szSQL = @szSQL + 'DECLARE @start_row_num AS INT;' + CHAR(13)
SET @szSQL = @szSQL + 'DECLARE @end_row_num AS INT;' + CHAR(13)
SET @szSQL = @szSQL + 'DECLARE @total_rows AS FLOAT;' + CHAR(13)

SET @szSQL = @szSQL + 'SET @page_size = ' + CAST(@PageSize AS NVARCHAR) + ';' + CHAR(13)
SET @szSQL = @szSQL + 'SET @current_page = ' + CAST(@PageIndex AS NVARCHAR) + ';' + CHAR(13)

-- 计算总页数
SET @szSQL = @szSQL + 'SELECT @total_rows = COUNT(*) FROM ' + @szTableName
IF LEN(@szWhere) > 0
BEGIN
	SET @szSQL = @szSQL + CHAR(13) + 'WHERE ' + @szWhere
END
SET @szSQL = @szSQL + ';' + CHAR(13)

SET @szSQL = @szSQL + 'SET @total_pages = CEILING(@total_rows / @page_size);' + CHAR(13)
SET @szSQL = @szSQL + 'IF @current_page > @total_pages' + CHAR(13)
SET @szSQL = @szSQL + '  SET @current_page = @total_pages;' + CHAR(13)

-- 列出指定页的内容
SET @szSQL = @szSQL + 'IF @current_page = 1' + CHAR(13)
SET @szSQL = @szSQL + 'BEGIN' + CHAR(13)
SET @szSQL = @szSQL + '  SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' + @szField + CHAR(13)
SET @szSQL = @szSQL + '  FROM ' + @szTableName + CHAR(13)
IF LEN(@szWhere) > 0
BEGIN
	SET @szSQL = @szSQL + '  WHERE ' + @szWhere + CHAR(13)
END
SET @szSQL = @szSQL + '  ORDER BY ' + @szOrder + CHAR(13)
SET @szSQL = @szSQL + 'END' + CHAR(13)
SET @szSQL = @szSQL + 'ELSE' + CHAR(13)
SET @szSQL = @szSQL + 'BEGIN' + CHAR(13)
SET @szSQL = @szSQL + '  SET @start_row_num = (@current_page - 1) * @page_size + 1;' + CHAR(13)
SET @szSQL = @szSQL + '  SET @end_row_num = @start_row_num + @page_size - 1;' + CHAR(13)

SET @szSQL = @szSQL + '  WITH t AS' + CHAR(13)
SET @szSQL = @szSQL + '  (' + CHAR(13)
SET @szSQL = @szSQL + '    SELECT ' + @szField + ', ROW_NUMBER(' +  + ') OVER(ORDER BY ' + @szOrder + ') AS row_number ' + CHAR(13)
SET @szSQL = @szSQL + '    FROM ' + @szTableName + CHAR(13)
IF LEN(@szWhere) > 0
BEGIN
	SET @szSQL = @szSQL + '    WHERE ' + @szWhere + CHAR(13)
END
SET @szSQL = @szSQL + '  )' + CHAR(13)
SET @szSQL = @szSQL + '  SELECT * from t' + CHAR(13)
SET @szSQL = @szSQL + '  WHERE row_number BETWEEN @start_row_num AND @end_row_num' + CHAR(13)
SET @szSQL = @szSQL + 'END' + CHAR(13)


SET @szSQL = @szSQL + 'SELECT @current_page AS PageNo, @total_pages AS TotalPages, CAST(@total_rows AS INT) AS TotalRows;'

--print @szSQL
EXEC(@szSQL)



GO