复制代码 代码如下:
set quoted_identifier on
go
set ansi_nulls on
go
alter 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 @type
declare @sql nvarchar(4000)
set @sql = ‘declare @sortcolumnbegin ‘ + @type + ‘
set rowcount ‘ + cast(@toprows as varchar(10))+ ‘ select @sortcolumnbegin=’ +
@sortcolumn + ‘ from ‘ + @tablenames + ‘ ‘ + @filter + ‘ ‘ + @group + ‘ order by ‘ + @order + ‘
set rowcount ‘ + cast(@pagesize as varchar(10)) + ‘
select ‘ + @fields + ‘ from ‘ + @tablenames + ‘ ‘ + @filter + ‘ and ‘ + @sortcolumn + ” + @operator + ‘@sortcolumnbegin ‘ + isnull(@group,”) + ‘ order by ‘ + @order + ”
— print(@sql)
exec(@sql)
end
go
set quoted_identifier off
go
set ansi_nulls on
go