AspNetPager分页控件 存储过程

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