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.nam
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''
與自定義分頁結合例子：
