ALTER PROCEDURE [dbo].[pagelist]
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int,--每页显示记录条数
@currentpage int,--第几页
@orderid nvarchar(50),--主键排序
@sort int,--排序方式,1表示升序,0表示降序排列
--@rowcount int output,--总记录数,共有几条信息
--@pagecount int output--总页数,共有多少页
@ziduan nvarchar(50)=null,
@stra nvarchar(50)=null--查询字段名
ASdeclare @rowcount int
declare @pagecount int
declare @countsql nvarchar(50)
declare @sql nvarchar(200)
declare @subsql nvarchar(100)--not in子sql语句
declare @tmpOrderid nvarchar(50)
--返回总记录数,并赋值给输出参数@rowcount
set @countsql='select @totalcount=count(*) from '+@tablename
exec sp_executesql @countsql,N'@totalcount int out',@rowcount output--判断字段名是否为空
if @fieldname is null or @fieldname=''
set @fieldname=' * '--判断是否排序及排序方式
if @orderid is null or @orderid=''
set @tmpOrderid=' '
else
begin
if @sort=0
set @tmpOrderid='order by '+@orderid+' desc'
else
set @tmpOrderid='order by '+@orderid+' asc'
end--计算页数
if @rowcount%@pagesize>0
set @pagecount =(@rowcount/@pagesize)+1;
else
set @pagecount=@rowcount/@pagesize;--分页算法实现
if @stra is null --用来判断是否为订单表的查询 (pagelist orderview,'*',5,2,'oid',0,'等待处理')
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
else
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' where '+@ziduan+ ' ='+@stra+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
exec(@sql)为什么这个IF..else语句没起到作用呢??
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int,--每页显示记录条数
@currentpage int,--第几页
@orderid nvarchar(50),--主键排序
@sort int,--排序方式,1表示升序,0表示降序排列
--@rowcount int output,--总记录数,共有几条信息
--@pagecount int output--总页数,共有多少页
@ziduan nvarchar(50)=null,
@stra nvarchar(50)=null--查询字段名
ASdeclare @rowcount int
declare @pagecount int
declare @countsql nvarchar(50)
declare @sql nvarchar(200)
declare @subsql nvarchar(100)--not in子sql语句
declare @tmpOrderid nvarchar(50)
--返回总记录数,并赋值给输出参数@rowcount
set @countsql='select @totalcount=count(*) from '+@tablename
exec sp_executesql @countsql,N'@totalcount int out',@rowcount output--判断字段名是否为空
if @fieldname is null or @fieldname=''
set @fieldname=' * '--判断是否排序及排序方式
if @orderid is null or @orderid=''
set @tmpOrderid=' '
else
begin
if @sort=0
set @tmpOrderid='order by '+@orderid+' desc'
else
set @tmpOrderid='order by '+@orderid+' asc'
end--计算页数
if @rowcount%@pagesize>0
set @pagecount =(@rowcount/@pagesize)+1;
else
set @pagecount=@rowcount/@pagesize;--分页算法实现
if @stra is null --用来判断是否为订单表的查询 (pagelist orderview,'*',5,2,'oid',0,'等待处理')
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
else
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' where '+@ziduan+ ' ='+@stra+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
exec(@sql)为什么这个IF..else语句没起到作用呢??
不管怎么写。,永远执行
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
if (@stra is null or @stra='')
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
else
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' where '+@ziduan+ ' ='+@stra+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
exec(@sql)
是不是else里边的语句写错了??我在过程内给@stra赋值出错提示消息 207,级别 16,状态 1,第 1 行
列名 '等待处理' 无效。
if (@stra is null or @stra='')
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
else
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' where '+@ziduan+ ' ='+@stra+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
print @sql
exec(@sql)
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int,--每页显示记录条数
@currentpage int,--第几页
@orderid nvarchar(50),--主键排序
@sort int,--排序方式,1表示升序,0表示降序排列
--@rowcount int output,--总记录数,共有几条信息
--@pagecount int output--总页数,共有多少页
@ziduan nvarchar(50)=null,
@stra nvarchar(50)=null--查询字段名你默认字段值为空!!!!!
去掉试试看??
为什么等待处理没有‘’呢?
if (@stra is null or @stra='')
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
else
begin
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' where '+@ziduan+ ' ='''+@stra+''' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
end
print @sql
exec(@sql)