我刚会写存储过程,写了一个下面的存储过程:
REATE PROCEDURE create_class
@s_name varchar(128),
@s_lenth varchar(64),
@s_out int OUTPUT
AS
SET XACT_ABORT on
BEGIN TRANSACTION crclass if(select count(*) from POBJECT,PCLASSINDEX where POBJECT.IOBJECT<>0 and POBJECT.IOBJECT = PCLASSINDEX.IOBJECT and POBJECT.SNAME = @s_name) = 0
begin
DECLARE @i_obj int update PNEWOBJECT set IOBJECT=IOBJECT+1 select @i_obj=iobject from PNEWOBJECT insert into POBJECT (IOBJECT, SNAME, ISERVER, ILOCAL,ITABLE,IREFERENCE,BSECURITY,IUSER) values(@i_obj, @s_name, 0, @i_obj, 1030,0,0,0) insert into PCLASSINDEX values(@i_obj,@s_lenth) set @s_out=1
return 1 end
else
set @s_out=0IF (@@ERROR <> 0) --这个来判断,成功:0 ;失败:非零
ROLLBACK TRANSACTION crclassELSE
COMMIT TRANSACTION crclass
SET XACT_ABORT off
Return @@ERROR
GO然后在查询分析器里写了入下语句
declare @ot int
exec create_class 'test','1,2,3' ,@ot OUT执行后就会报266错误我想以后在程序中调用这个存储过程,然后把回滚的操作留给存储过程中完成,这样写有什么问题吗?我数据库实在是只懂皮毛。这个问题在这里应该很简单吧。
REATE PROCEDURE create_class
@s_name varchar(128),
@s_lenth varchar(64),
@s_out int OUTPUT
AS
SET XACT_ABORT on
BEGIN TRANSACTION crclass if(select count(*) from POBJECT,PCLASSINDEX where POBJECT.IOBJECT<>0 and POBJECT.IOBJECT = PCLASSINDEX.IOBJECT and POBJECT.SNAME = @s_name) = 0
begin
DECLARE @i_obj int update PNEWOBJECT set IOBJECT=IOBJECT+1 select @i_obj=iobject from PNEWOBJECT insert into POBJECT (IOBJECT, SNAME, ISERVER, ILOCAL,ITABLE,IREFERENCE,BSECURITY,IUSER) values(@i_obj, @s_name, 0, @i_obj, 1030,0,0,0) insert into PCLASSINDEX values(@i_obj,@s_lenth) set @s_out=1
return 1 end
else
set @s_out=0IF (@@ERROR <> 0) --这个来判断,成功:0 ;失败:非零
ROLLBACK TRANSACTION crclassELSE
COMMIT TRANSACTION crclass
SET XACT_ABORT off
Return @@ERROR
GO然后在查询分析器里写了入下语句
declare @ot int
exec create_class 'test','1,2,3' ,@ot OUT执行后就会报266错误我想以后在程序中调用这个存储过程,然后把回滚的操作留给存储过程中完成,这样写有什么问题吗?我数据库实在是只懂皮毛。这个问题在这里应该很简单吧。
return 1
=====改成
set @s_out=1
begin
COMMIT TRANSACTION crclass
return 1
end
return 1 end
else多了一个return肯定会报错的。