功能不错的分页存储过程，可支持多表查询，任意排序
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)='',--分组依据，可以为空，不用填groupby
@Order VARCHAR(200)=''--排序，可以为空，为空默认按主键升序排列，不用填orderby
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='Where1=1'
ELSE
SET @Filter='Where'+@Filter
IF @Group<>''
SET @Group='GROUPBY'+@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 sysobjectso
JOIN syscolumnsc on o.id=c.id
JOIN systypest 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+'
SETROWCOUNT'+@TopRows+'
Select @SortColumnBegin='+@SortColumn+'FROM'+@TableNames+''+@Filter+''+@Group+'orDERBY'+@Order+'
SETROWCOUNT'+@PageSize+'
Select '+@Fields+'FROM'+@TableNames+''+@Filter+'AND'+@SortColumn+''+@Operator+'@SortColumnBegin'+@Group+'order by'+@Order+'
')
END
GO
--调用例子：
--1.单表/单排序
-- EXECusp_PagingLarge'bigtable','d_id','d_id,d_title,d_content,d_time',20,1,'','','d_iddesc'
--2.单表/多排序
--EXECusp_PagingLarge'bigtable','d_id','*',20,0,'','','d_timeasc,d_iddesc'
--3.多表/单排序
--EXECusp_PagingLarge'bigtableleftjoinbigtable_authoronbigtable.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_idasc'
--4.多表/多排序
--EXECusp_PagingLarge'bigtableleftjoinbigtable_authoronbigtable.d_id=bigtable_author.BigTable_id','bigtable.d_id','bigtable.d_id,bigtable.d
