我有一个存储过程
CREATE PROCEDURE [dbo].[SP_TEST]
ASINSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2 WHERE B3='1';UPDATE T2 SET B4=GETDATE()
WHERE B3='1';UPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2 WHERE B3='1' );GO
--------------------------------------
如何在存储过程中判断3个语句都执行完毕?
如果三条语句都执行成功,则返回 0
如果其中只要有语句报错,则回滚,并返回错误-1这个判断应该如何写啊,谢谢
CREATE PROCEDURE [dbo].[SP_TEST]
ASINSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2 WHERE B3='1';UPDATE T2 SET B4=GETDATE()
WHERE B3='1';UPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2 WHERE B3='1' );GO
--------------------------------------
如何在存储过程中判断3个语句都执行完毕?
如果三条语句都执行成功,则返回 0
如果其中只要有语句报错,则回滚,并返回错误-1这个判断应该如何写啊,谢谢
alter PROCEDURE [dbo].[SP_TEST]
AS
begin try
begin tran
INSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2 WHERE B3='1';UPDATE T2 SET B4=GETDATE()
WHERE B3='1';UPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2 WHERE B3='1' );commit tran
end try
begin catch
rollback
raiserror('出错了',16,1)
end catch
alter PROCEDURE [dbo].[SP_TEST]
AS
begin try
begin tran
INSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2 WHERE B3='1';UPDATE T2 SET B4=GETDATE()
WHERE B3='1';UPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2 WHERE B3='1' );commit tran
return 0
end try
begin catch
rollback
return -1
end catch
( @RetVal int=0 output)
AS
BeginBegin TRANSACTIONINSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2 WHERE B3='1';If @@ERROR<>0
Goto ErrmUPDATE T2 SET B4=GETDATE()
WHERE B3='1';If @@ERROR<>0
Goto ErrmUPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2 WHERE B3='1' );If @@ERROR<>0
Goto ErrmCOMMIT TRANSACTION
Return @Retval ErrM:
ROLLBACK TRANSACTION
Set @Retval = -1
Return @Retval End
GO
set xact_abort on
try catch
[/code]