CREATE PROCEDURE [dbo].[PagingLarge] @TableNames VARCHAR(300), --表名,可以是多个表,但不能用别名 @PrimaryKey VARCHAR(200), --主键 @Fields VARCHAR(400)='', --要取出的字段,可以是多个表的字段,可以为空,为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页,0表示第1页 @Filter VARCHAR(400) = '', --条件,可以为空,不用填 where @Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by @Order VARCHAR(400) = '', --排序,可以为空,为空默认按主键升序排列,不用填 order by @GetTotal Varchar(100) --取记录总数 AS BEGIN DECLARE @Operator CHAR(10) declare @start int declare @count int declare @end int declare @psize int set @start = (@CurrentPage - 1) * @PageSize + 1 set @end= @start + @PageSize - 1 declare @sql nvarchar(500)
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 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 = ' ASC' ELSE SET @Operator = ' DESC' END ELSE SET @Operator = ' ASC' ELSE SET @Operator = ' DESC' end else SET @Operator = ' DESC'
set @sql = 'select @count=count(' + @GetTotal + ') from ' + @TableNames + ' ' + @Filter + '' exec sp_executesql @sql,N'@count int out ',@count out
set @psize = @PageSize if @end>@count begin set @end=@count set @psize = @count - @start+1 if @psize<0 set @psize=0 end exec(' Select ' + @Fields + ' into #tmp FROM ' + @TableNames + ' ' + @Filter + @Group + ' orDER BY ' + @Order + ' select top '+@psize+' * from #tmp where '+@PrimaryKey+' in (select top '+@end+' '+@PrimaryKey+' from #tmp) order by '+@PrimaryKey+' '+@Operator+'
drop table #tmp ') select @count as Total END
如果有什么错误希望大家指正