我写了这样一个存储过程,结构如下:
CREATE PROCEDURE create_lib
@libname varchar(40) ,
as
SET XACT_ABORT on
BEGIN TRANSACTION libbegin
DECLARE @libid int,@tbl_1 int,@tbl_2 int,@tbl_3 int,@fold_id int,@dic_id1 int,@dic_id2 int,@dic_id3 int
update PNEWOBJECT set IOBJECT=IOBJECT+1 select @fold_id=IOBJECT from PNEWOBJECT insert into POBJECT (IOBJECT, SNAME, ITABLE,IREFERENCE) values(@fold_id, '', 1038,0) insert into PFILELIB (IOBJECT ) values ( @fold_id ) update POBJECT set SNAME = '库未组卷文件夹' where IOBJECT=@fold_id insert into PFOLDER ( ISET , IITEM ,IID) values ( @libid,@fold_id ,@libid) insert into PREFERENCE ( IDEST , ISRC ,IID) values( @fold_id,@libid,@libid) update POBJECT set IREFERENCE=IREFERENCE+1 where IOBJECT=@fold_id
(这里给这些变量都赋了值,执行也是正确的)
(这里调用了三个存储过程,执行正常)update PLIB set 各级分类码长度=@class_l,库案卷表ID=@tbl_1,库文件表ID=@tbl_2,库文件关系表ID=@tbl_3,库未组卷文件夹ID=@fold_id,库字典表ID=@dic_id1,库文件字典表ID=@dic_id2,库文件关系字典表ID=@dic_id3 where IOBJECT=@libid
set @s1='insert into LIB'+ Convert(varchar(10),@libid)+'VOLUMNTBL (IOBJECT,销毁标识,标题,分类号,创建日期) values (@fold_id,2,'''','''',getdate())' exec (@s1)
END
IF (@@ERROR <> 0) --这个来判断成功:0;失败:非零 ROLLBACK TRANSACTION lib ELSE
COMMIT TRANSACTION lib SET XACT_ABORT off RETURN @@ERROR
go现在我执行这个存储过程,就会报错:
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@fold_id'。我不是声明国这个变量了吗?怎么解决这样的错误,谢了!
CREATE PROCEDURE create_lib
@libname varchar(40) ,
as
SET XACT_ABORT on
BEGIN TRANSACTION libbegin
DECLARE @libid int,@tbl_1 int,@tbl_2 int,@tbl_3 int,@fold_id int,@dic_id1 int,@dic_id2 int,@dic_id3 int
update PNEWOBJECT set IOBJECT=IOBJECT+1 select @fold_id=IOBJECT from PNEWOBJECT insert into POBJECT (IOBJECT, SNAME, ITABLE,IREFERENCE) values(@fold_id, '', 1038,0) insert into PFILELIB (IOBJECT ) values ( @fold_id ) update POBJECT set SNAME = '库未组卷文件夹' where IOBJECT=@fold_id insert into PFOLDER ( ISET , IITEM ,IID) values ( @libid,@fold_id ,@libid) insert into PREFERENCE ( IDEST , ISRC ,IID) values( @fold_id,@libid,@libid) update POBJECT set IREFERENCE=IREFERENCE+1 where IOBJECT=@fold_id
(这里给这些变量都赋了值,执行也是正确的)
(这里调用了三个存储过程,执行正常)update PLIB set 各级分类码长度=@class_l,库案卷表ID=@tbl_1,库文件表ID=@tbl_2,库文件关系表ID=@tbl_3,库未组卷文件夹ID=@fold_id,库字典表ID=@dic_id1,库文件字典表ID=@dic_id2,库文件关系字典表ID=@dic_id3 where IOBJECT=@libid
set @s1='insert into LIB'+ Convert(varchar(10),@libid)+'VOLUMNTBL (IOBJECT,销毁标识,标题,分类号,创建日期) values (@fold_id,2,'''','''',getdate())' exec (@s1)
END
IF (@@ERROR <> 0) --这个来判断成功:0;失败:非零 ROLLBACK TRANSACTION lib ELSE
COMMIT TRANSACTION lib SET XACT_ABORT off RETURN @@ERROR
go现在我执行这个存储过程,就会报错:
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@fold_id'。我不是声明国这个变量了吗?怎么解决这样的错误,谢了!
set @s1='insert into LIB'+ Convert(varchar(10),@libid)+'VOLUMNTBL (IOBJECT,销毁标识,标题,分类号,创建日期) values (@fold_id,2,'''','''',getdate())'
改为:
set @s1='insert into LIB'+ Convert(varchar(10),@libid)+'VOLUMNTBL (IOBJECT,销毁标识,标题,分类号,创建日期) values (' + rtrim(@fold_id) /*修改此处*/
+ ',2,'''','''',getdate())'