欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

AspNetPager分页控件 存储过程

程序员文章站 2023-11-20 22:23:28
复制代码 代码如下: set quoted_identifier on go set ansi_nulls on go alter procedure usp_paging...
复制代码 代码如下:

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