复制代码 代码如下:
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