请教下存储过程中事务的基本写法……


请问存储过程中，下面这个事务的写法对了么？

1、不需要返回值，不是两条SQL语句一起执行的话就回滚。
2、SQL操作语句1和语句2都正确，正在用的。现在是想加个事务，防止出现意外情况导致两个操作不同步。

USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[存储过程名]
@id int
AS
BEGIN
BEGIN TRANSACTION
    SQL操作语句1
    SQL操作语句2
IF @@ERROR>0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END

[解决办法]
如果只有两个语句，那每句都加事务，然后最外层再加，这样就比较保险
[解决办法]
楼上说的没错，如果你的MSSQL版本是2005及以上，建议用try catch
http://msdn.microsoft.com/EN-US/library/ms175976.aspx


BEGIN TRANSACTION;

BEGIN TRY
SQL操作语句1
SQL操作语句2
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO