说明：1.支持多表查询 2. 支持任意排序 3. 不支持表别名 参考了

IF (
		EXISTS (
			SELECT *
			FROM sysobjects
			WHERE [id] = OBJECT_ID('usp_PagingLarge')
				AND xtype = 'P'
			)
		)
	DROP PROCEDURE usp_PagingLarge * /
GO

CREATE PROCEDURE usp_PagingLarge @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名
	@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
	@Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
	@PageSize INT, --每页记录数
	@CurrentPage INT, --当前页，0表示第1页
	@Filter VARCHAR(200) = '', --条件，可以为空，不用填 where
	@Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by
	@Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
	DECLARE @SortColumn VARCHAR(200)
	DECLARE @Operator CHAR(2)
	DECLARE @SortTable VARCHAR(200)
	DECLARE @SortName VARCHAR(200)

	IF @Fields = ''
		SET @Fields = '*'

	IF @Filter = ''
		SET @Filter = 'WHERE 1=1'
	ELSE
		SET @Filter = 'WHERE ' + @Filter

	IF @Group <> ''
		SET @Group = 'GROUP BY ' + @Group

	IF @Order <> ''
	BEGIN
		DECLARE @pos1 INT, @pos2 INT

		SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')

		IF CHARINDEX(' DESC', @Order) > 0
			IF CHARINDEX(' ASC', @Order) > 0
			BEGIN
				IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
					SET @Operator = '<='
				ELSE
					SET @Operator = '>='
			END
			ELSE
				SET @Operator = '<='
		ELSE
			SET @Operator = '>='

		SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
		SET @pos1 = CHARINDEX(',', @SortColumn)

		IF @pos1 > 0
			SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1 - 1)
		SET @pos2 = CHARINDEX('.', @SortColumn)

		IF @pos2 > 0
		BEGIN
			SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2 - 1)

			IF @pos1 > 0
				SET @SortName = SUBSTRING(@SortColumn, @pos2 + 1, @pos1 - @pos2 - 1)
			ELSE
				SET @SortName = SUBSTRING(@SortColumn, @pos2 + 1, LEN(@SortColumn) - @pos2)
		END
		ELSE
		BEGIN
			SET @SortTable = @TableNames
			SET @SortName = @SortColumn
		END
	END
	ELSE
	BEGIN
		SET @SortColumn = @PrimaryKey
		SET @SortTable = @TableNames
		SET @SortName = @SortColumn
		SET @Order = @SortColumn
		SET @Operator = '>='
	END

	DECLARE @type VARCHAR(50)
	DECLARE @prec INT

	SELECT @type = t.NAME, @prec = c.prec
	FROM sysobjects o
	JOIN syscolumns c ON o.id = c.id
	JOIN systypes t ON c.xusertype = t.xusertype
	WHERE o.NAME = @SortTable
		AND c.NAME = @SortName

	IF CHARINDEX('char', @type) > 0
		SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')'

	DECLARE @TopRows INT

	SET @TopRows = @PageSize * @CurrentPage + 1

	PRINT @TopRows
	PRINT @Operator

	EXEC (
			'
        DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
    '
			)
END
GO

–调用例子：

--1.单表/单排序
EXEC usp_PagingLarge 'bigtable', 'd_id', 'd_id,d_title,d_content,d_time', 20, 1, '', '', 'd_id desc'

--2.单表/多排序
EXEC usp_PagingLarge 'bigtable', 'd_id', '*', 20, 0, '', '', 'd_time asc,d_id desc'

--3.多表/单排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'

--4.多表/多排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'