set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[add_equip]
(
@id int output,
@equip_name varchar(100),
)
as
begin
begin transaction tran1;
INSERT INTO [equip](
equip_name
)
VALUES(
@equip_name
);
set @id = @@identity; insert into[equipment_base](equip_id,equip_type_id) values(@id,1); commit transaction tran1;
if @@error<>0
begin
set @id = 0;
rollback transaction tran1;
end
end
编译没问题,执行存储过程时,报错:
消息 3903,级别 16,状态 1,过程 add_equip,
ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
不是已经有begin tran了吗?
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[add_equip]
(
@id int output,
@equip_name varchar(100),
)
as
begin
begin transaction tran1;
INSERT INTO [equip](
equip_name
)
VALUES(
@equip_name
);
set @id = @@identity; insert into[equipment_base](equip_id,equip_type_id) values(@id,1); commit transaction tran1;
if @@error<>0
begin
set @id = 0;
rollback transaction tran1;
end
end
编译没问题,执行存储过程时,报错:
消息 3903,级别 16,状态 1,过程 add_equip,
ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
不是已经有begin tran了吗?
commit transaction tran1;
else
begin
set @id = 0;
rollback transaction tran1;
end
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[add_equip]
(
@id int output,
@equip_name varchar(100),
)
as
begin
begin transaction tran1; --1
INSERT INTO [equip] (equip_name ) VALUES(@equip_name);
set @id = @@identity;insert into [equipment_base] (equip_id,equip_type_id) values(@id,1); commit transaction tran1; --1
if @@error<>0
begin
set @id = 0;
rollback transaction tran1; --2 ???
end
end
现在这种情况应该怎么写呢?
if @@error=0
commit transaction tran1;
else
begin
set @id = 0;
rollback transaction tran1;
end
分号表示语句结束
begin transaction tran1
INSERT INTO [equip](equip_name)VALUES(@equip_name)
set @id = @@identityinsert into[equipment_base](equip_id,equip_type_id) values(@id,1)commit transaction tran1
if @@error<>0
begin
set @id = 0;
rollback transaction tran1
end