SQL SERVER 預存程序 使用 TRANSACTION

SQL SERVER 2005新增了預存程序的 TRY ..... CATCH 使用上真的方便多了 1.舊式TRANSACTION用法(ASP.NET呼叫此預存程序可傳回指定錯誤說明)

.........
AS
BEGIN TRANSACTION
DECLARE @MSG varchar(30)
BEGIN
IF NOT EXISTS( ...... )
 BEGIN
  SET @MSG = 'a_fail'
  GOTO Error_Handler
 END
ELSE
 BEGIN
  DELETE .....
  SET @MSG = 'true'
 END
  
IF @MSG = 'true'
 BEGIN
    IF NOT EXISTS( ...... )
     BEGIN
      SET @MSG = 'b_fail'
      GOTO Error_Handler
     END
    ELSE
     BEGIN
      DELETE .....
      SET @MSG = 'true'
     END
 END
COMMIT TRAN
Error_Handler:
IF @MSG = 'a_fail' OR @MSG = 'b_fail'
 BEGIN
  ROLLBACK TRANSACTION
 END
SELECT @MSG
END

2. TRANSACTION加入 TRY ..... CATCH 用法(回傳false表示寫入失敗)

.........
AS
BEGIN TRY
 BEGIN TRANSACTION
  DECLARE @MSG varchar(30)
  SET @MSG = 'true'
  INSERT INTO ......
  INSERT INTO ......
  COMMIT TRANSACTION
 END TRY
BEGIN CATCH
  SET @MSG = 'false'
  ROLLBACK TRANSACTION
END CATCH
SELECT @MSG