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

sqlserver 存储过程分页代码第1/2页

程序员文章站 2023-12-16 18:22:10
复制代码 代码如下:declare @totalcount int declare @totalpagecount int exec p_viewpage_a 'type1...
复制代码 代码如下:

declare @totalcount int
declare @totalpagecount int
exec p_viewpage_a 'type1','*','id','','id asc',1,0,4,3,@totalcount output,@totalpagecount output

select * from type1


create proc p_viewpage_a
/*
nzperfect [no_miss] 高效通用分页存储过程(双向检索) 2007.5.7 qq:34813284
敬告:适用于单一主键或存在唯一值列的表或视图
ps:sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@tablename varchar(200), --表名
@fieldlist varchar(2000), --显示列名,如果是全部字段则为*
@primarykey varchar(100), --单一主键或唯一值键
@where varchar(2000), --查询条件 不含'where'字符,如id>10 and len(userid)>9
@order varchar(1000), --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc
--注意当@sorttype=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷
@sorttype int, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@recordercount int, --记录总数 0:会返回总记录
@pagesize int, --每页输出的记录数
@pageindex int, --当前页数
@totalcount int output, --记返回总记录
@totalpagecount int output --返回总页数
as
set nocount on
if isnull(@totalcount,'') = '' set @totalcount = 0
set @order = rtrim(ltrim(@order))
set @primarykey = rtrim(ltrim(@primarykey))
set @fieldlist = replace(rtrim(ltrim(@fieldlist)),' ','')
while charindex(', ',@order) > 0 or charindex(' ,',@order) > 0
begin
set @order = replace(@order,', ',',')
set @order = replace(@order,' ,',',')
end
if isnull(@tablename,'') = '' or isnull(@fieldlist,'') = ''
or isnull(@primarykey,'') = ''
or @sorttype < 1 or @sorttype >3
or @recordercount < 0 or @pagesize < 0 or @pageindex < 0
begin
print('err_00')
return
end
if @sorttype = 3
begin
if (upper(right(@order,4))!=' asc' and upper(right(@order,5))!=' desc')
begin print('err_02') return end
end
declare @new_where1 varchar(1000)
declare @new_where2 varchar(1000)
declare @new_order1 varchar(1000)
declare @new_order2 varchar(1000)
declare @new_order3 varchar(1000)
declare @sql varchar(8000)
declare @sqlcount nvarchar(4000)
if isnull(@where,'') = ''
begin
set @new_where1 = ' '
set @new_where2 = ' where '
end
else
begin
set @new_where1 = ' where ' + @where
set @new_where2 = ' where ' + @where + ' and '
end
if isnull(@order,'') = '' or @sorttype = 1 or @sorttype = 2
begin
if @sorttype = 1
begin
set @new_order1 = ' order by ' + @primarykey + ' asc'
set @new_order2 = ' order by ' + @primarykey + ' desc'
end
if @sorttype = 2
begin
set @new_order1 = ' order by ' + @primarykey + ' desc'
set @new_order2 = ' order by ' + @primarykey + ' asc'
end
end
else
begin
set @new_order1 = ' order by ' + @order
end
if @sorttype = 3 and charindex(','+@primarykey+' ',','+@order)>0
begin
set @new_order1 = ' order by ' + @order
set @new_order2 = @order + ','
set @new_order2 = replace(replace(@new_order2,'asc,','{asc},'),'desc,','{desc},')
set @new_order2 = replace(replace(@new_order2,'{asc},','desc,'),'{desc},','asc,')
set @new_order2 = ' order by ' + substring(@new_order2,1,len(@new_order2)-1)
if @fieldlist <> '*'
begin
set @new_order3 = replace(replace(@order + ',','asc,',','),'desc,',',')
set @fieldlist = ',' + @fieldlist
while charindex(',',@new_order3)>0
begin
if charindex(substring(','+@new_order3,1,charindex(',',@new_order3)),','+@fieldlist+',')>0
begin
set @fieldlist =
@fieldlist + ',' + substring(@new_order3,1,charindex(',',@new_order3))
end
set @new_order3 =
substring(@new_order3,charindex(',',@new_order3)+1,len(@new_order3))
end
set @fieldlist = substring(@fieldlist,2,len(@fieldlist))
end
end
set @sqlcount = 'select @totalcount=count(*),@totalpagecount=ceiling((count(*)+0.0)/'
+ cast(@pagesize as varchar)+') from ' + @tablename + @new_where1
if @recordercount = 0
begin
exec sp_executesql @sqlcount,n'@totalcount int output,@totalpagecount int output',
@totalcount output,@totalpagecount output
end
else
begin
select @totalcount = @recordercount
end
if @pageindex > ceiling((@totalcount+0.0)/@pagesize)
begin
set @pageindex = ceiling((@totalcount+0.0)/@pagesize)
end
if @pageindex = 1 or @pageindex >= ceiling((@totalcount+0.0)/@pagesize)
begin
if @pageindex = 1 --返回第一页数据
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where1 + @new_order1
end
if @pageindex >= ceiling((@totalcount+0.0)/@pagesize) --返回最后一页数据
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(abs(@pagesize*@pageindex-@totalcount-@pagesize))
+ ' ' + @fieldlist + ' from '
+ @tablename + @new_where1 + @new_order2 + ' ) as tmp '
+ @new_order1
end
end
else
begin
if @sorttype = 1 --仅主键正序排序
begin
if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' > '
+ '(select max(' + @primarykey + ') from (select top '
+ str(@pagesize*(@pageindex-1)) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order1 +' ) as tmp) '+ @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(@pagesize) + ' '
+ @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' < '
+ '(select min(' + @primarykey + ') from (select top '
+ str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2
+ ' ) as tmp ' + @new_order1
end
end
if @sorttype = 2 --仅主键反序排序
begin
if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' < '
+ '(select min(' + @primarykey + ') from (select top '
+ str(@pagesize*(@pageindex-1)) + ' ' + @primarykey
+' from '+ @tablename
+ @new_where1 + @new_order1 + ') as tmp) '+ @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(@pagesize) + ' '
+ @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' > '
+ '(select max(' + @primarykey + ') from (select top '
+ str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2
+ ' ) as tmp ' + @new_order1
end
end
if @sorttype = 3 --多列排序,必须包含主键,且放置最后,否则不处理
begin
if charindex(',' + @primarykey + ' ',',' + @order) = 0
begin print('err_02') return end
if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ ' select top ' + str(@pagesize*@pageindex) + ' ' + @fieldlist
+ ' from ' + @tablename + @new_where1 + @new_order1 + ' ) as tmp '
+ @new_order2 + ' ) as tmp ' + @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ ' select top ' + str(@totalcount-@pagesize *@pageindex+@pagesize) + ' ' + @fieldlist
+ ' from ' + @tablename + @new_where1 + @new_order2 + ' ) as tmp '
+ @new_order1 + ' ) as tmp ' + @new_order1
end
end
end
exec(@sql)
go

如何用vc#调用上面的存储过程

1

上一篇:

下一篇: