万能分页存储过程

这是项目中用到的一个存储过程，拿出来分享给大家。

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[sp_PageData]
	@TableName NVARCHAR(50),
	@SelectFields NVARCHAR(500),
	@PageIndex INT,
	@PageSize INT,
	@Condition NVARCHAR(4000),
	@Sort NVARCHAR(100),
	@RowCount INT output
AS
BEGIN
	IF LEN(@Condition) > 0
	BEGIN
		SET @Condition = ' WHERE ' + @Condition;
	END
	IF LEN(@Sort) = 0
	BEGIN
		SET @Sort = 'id DESC';
	END
	--获取@RowCount信息
	DECLARE @sql NVARCHAR(4000)
	SET @sql = 'SELECT @TEMP = COUNT(id) FROM ' + @TableName + @Condition
	EXEC sp_executesql @sql, N'@TEMP INT OUTPUT', @RowCount OUTPUT
	DECLARE @StartRowIndex INT;
	--根据页码和每页大小计算起始行
	IF @PageIndex < 1
	BEGIN
		SET @PageIndex = 1
	END
	DECLARE @PageCount INT
	SET @PageCount = ceiling(cast(@RowCount as float) / @PageSize)
	IF @PageIndex > @PageCount
	BEGIN
		SET @PageIndex = @PageCount
	END
	SET @StartRowIndex = ( @PageIndex - 1 )* @PageSize + 1
	EXEC
	('
	 --声明一个读取主键的游标
	 DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
	 --这里只读取主键，并插入表里
	 SELECT id FROM ' + @TableName + @Condition + ' ORDER BY ' + @Sort
	)
	DECLARE @IDs NVARCHAR(2000)
	SET @IDs = '0'
	DECLARE @PK NVARCHAR(50)
	--打开游标
	OPEN PagingCursor
	--直接跳到起始行
	FETCH RELATIVE @StartRowIndex FROM PagingCursor INTO @PK
	--不返回统计的行数
	SET NOCOUNT ON
	--开始循环读取记录
	WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
	BEGIN
	   --INSERT @tblPK (PK)  VALUES ( @PK )
	   SET @IDs = @IDs + ',' + @PK
	   FETCH NEXT FROM PagingCursor INTO @PK
	   SET @PageSize = @PageSize - 1
	END
	CLOSE       PagingCursor
	DEALLOCATE  PagingCursor
	EXEC('SELECT ' + @SelectFields + ' FROM ' + @TableName
 + ' WHERE ID IN (' + @IDs+ ') ORDER BY ' + @Sort )
END