请教一个SQL存储过程判断问题，谢谢

[talentmykaka]

我有一个存储过程
CREATE PROCEDURE [dbo].[SP_TEST]
AS

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' );

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

[Ginnnnnnnn]

使用try catch 即可

[半杯清茶半杯酒]

Alter PROCEDURE [dbo].[SP_TEST]
( @RetVal int=0 output)
AS
Begin

Begin TRANSACTION

INSERT T1 (A1,A2,A3,B1,B2,B3)
SELECT A1,A2,A3,B1,B2,B3 FROM T2  WHERE B3='1';

If @@ERROR<>0
Goto Errm

UPDATE T2 SET B4=GETDATE()
WHERE B3='1';

If @@ERROR<>0
Goto Errm

UPDATE T3 SET B4=GETDATE()
WHERE A1 IN ( SELECT A1 FROM T2  WHERE B3='1' );

If @@ERROR<>0
Goto Errm

COMMIT TRANSACTION
    
Return @Retval  

ErrM:   
ROLLBACK TRANSACTION     
Set @Retval = -1  
Return @Retval  

End
GO

[lbyeye]

都是高手，学习了

[kingtiy]

set xact_abort on
try catch