存储过程调用,错误提示没有声明变量
存储过程
CREATE PROCEDURE jmtppage
@parentname varchar(255), --专辑主类
@oneclassname varchar(255), --专辑大类
@twoclassname varchar(255), --专辑小类
@curpage int, --当前页数
@numberpage int, --每页显示记录数
@totalpages int output, --总共页数
@num int output --总共记录数
AS
Set nocount on
declare @sql varchar(500) --动态sql语句
declare @beginrow int --每页开始的行数
declare @endrow int --每页结束的行数
Set @beginrow = 0
Set @endrow = 0
Set @num = 0
/*计算总记录数,并得到总页数*/
select @sql = 'select @num=count(*) from special where parentname='''+@parentname+''''
if @oneclassname != ''
begin
select @sql = @sql + ' and oneclassname='''+@oneclassname+''''
end
if @twoclassname != ''
begin
select @sql = @sql + ' and twoclassname='''+@twoclassname+''''
end
execute(@sql)
if @num % @numberpage = 0
begin
Set @totalpages = @num / @numberpage
end
else
begin
Set @totalpages = @num /@numberpage + 1
end
if @curpage <= 1
begin
Set @beginrow = 1
end
else if @curpage >= @totalpages
begin
Set @beginrow = (@totalpages-1)*@numberpage+1
end
else
begin
Set @beginrow = (@curpage-1)*@numberpage+1
end
/* 如果碰到不满页的情况处理,发生在最后一页的情况 */
if (@totalpages - @beginrow + 1) < @numberpage
begin
Set @endrow = @num
end
else
begin
Set @endrow = @beginrow + @numberpage -1
end
/*显示记录集的主体*/
select @sql = 'select * from special where parentname='''+@parentname+''''
if @oneclassname != ''
begin
select @sql = @sql + ' and oneclassname='''+@oneclassname+''''
end
if @twoclassname != ''
begin
select @sql = @sql + ' and twoclassname='''+@twoclassname+''''
end
execute(@sql)
GO
页面调用
if curpage < 1 then curpage = 1
const numberpage = 16
with cmd
.ActiveConnection = conn
.CommandText = "jmtppage"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("",adInteger,adParamReturnvalue)
.Parameters.Append .CreateParameter("@parentname",adVarchar,adParamINput,255,parentname)
.Parameters.Append .CreateParameter("@oneclassname",adVarchar,adParamINput,255,oneclassname)
.Parameters.Append .CreateParameter("@twoclassname",adVarchar,adParamINput,255,twoclassname)
.Parameters.Append .CreateParameter("@curpage",adInteger,adParamINput,,curpage)
.Parameters.Append .CreateParameter("@numberpage",adInteger,adParamINput,,numberpage)
.Parameters.Append .CreateParameter("@totalpages",adInteger,adParamOutput)
.Parameters.Append .CreateParameter("@num",adInteger,adParamOutput) ‘这一行报错
.Execute()
ReturnVal = .Parameters(0) '获取存储过程错误状态,0为正常,非0发生错误
totalpages = .Parameters("@totalpages")' 添加总页数到Pagecount变量
if curpage > totalpages then curpage = totalpages
num = .Parameters("@num")
set rst = cmd.execute()
end With
报错的提示是:必须声明变量 '@num'。