示例数据库为SQLSERVER2000下的NorthWind
请问@@ERROR用法如何,我一直以为类似其它程序中的try except功能可以自动捕获异常,执行语句出错后最后会跳到
if @@ERROR = 0这句上来,如果我想调用以下存储过程,改如何修改。谢谢CREATE PROCEDURE test
@cName nvarchar(15),
@CompanyName nvarchar(15),
@phone nvarchar(15),
@res nvarchar(100) output
AS
begin tran
insert into region(RegionID,RegionDescription) values (null,@cName) --执行这句出错,然后存储过程就终止了
insert into shippers(CompanyName,phone) values(@CompanyName,@phone)
if @@ERROR = 0
begin
commit tran
set @res ='成功'
select @res
end else
begin
rollback tran
set @res ='失败'
select @res
end
GO
请问@@ERROR用法如何,我一直以为类似其它程序中的try except功能可以自动捕获异常,执行语句出错后最后会跳到
if @@ERROR = 0这句上来,如果我想调用以下存储过程,改如何修改。谢谢CREATE PROCEDURE test
@cName nvarchar(15),
@CompanyName nvarchar(15),
@phone nvarchar(15),
@res nvarchar(100) output
AS
begin tran
insert into region(RegionID,RegionDescription) values (null,@cName) --执行这句出错,然后存储过程就终止了
insert into shippers(CompanyName,phone) values(@CompanyName,@phone)
if @@ERROR = 0
begin
commit tran
set @res ='成功'
select @res
end else
begin
rollback tran
set @res ='失败'
select @res
end
GO
@cName NVARCHAR(15),
@CompanyName NVARCHAR(15),
@phone NVARCHAR(15),
@res NVARCHAR(100) OUTPUT
AS
DECLARE @er INT
BEGIN TRAN
INSERT INTO region( RegionID, RegionDescription ) VALUES( NULL, @cName )--执行这句出错,然后存储过程就终止了
SET @er=@@ERROR
INSERT INTO shippers(CompanyName, phone) VALUES(@CompanyName, @phone)
SET @er=@er+@@ERROR
IF @@ERROR=0
BEGIN
COMMIT TRAN
SET @res = '成功'
SELECT @res
END
ELSE
BEGIN
ROLLBACK TRAN
SET @res = '失败'
SELECT @res
END
GO
@cName NVARCHAR(15),
@CompanyName NVARCHAR(15),
@phone NVARCHAR(15),
@res NVARCHAR(100) OUTPUT
AS
DECLARE @er INT
BEGIN TRAN
INSERT INTO region( RegionID, RegionDescription ) VALUES( NULL, @cName )--执行这句出错,然后存储过程就终止了
SET @er=@@ERROR
INSERT INTO shippers(CompanyName, phone) VALUES(@CompanyName, @phone)
SET @er=@er+@@ERROR
IF @er=0
BEGIN
COMMIT TRAN
SET @res = '成功'
SELECT @res
END
ELSE
BEGIN
ROLLBACK TRAN
SET @res = '失败'
SELECT @res
END
GO改一下
SET @er=abs(@@ERROR)
SET @er=@er+abs(@@ERROR)
@@ERROR有可能为负
正负等0也可能
CREATE PROCEDURE test
@cName nvarchar(15),
@CompanyName nvarchar(15),
@phone nvarchar(15),
@res nvarchar(100) output
AS
begin try
begin tran
insert into region(RegionID,RegionDescription) values (null,@cName);
insert into shippers(CompanyName,phone) values(@CompanyName,@phone);
set @res ='成功';
commit tran
end try
begin catch
set @res ='失败';
rollback tran;
end catchselect @res;
GO