[MSSQL] BEGIN TRY ... CATCH

在Stored Procedures裡面加上 BEGIN TRY ... CATCH，從中加上交易控制，若發生錯誤就返回並丟出錯誤訊息。

具體寫法

BEGIN TRY

write your code...

END TRY

BEGIN CATCH

END CATCH

但在實際應用上，我會顯示錯誤訊息並發送E-MAIL給相關聯絡人...

BEGIN TRY

BEGIN TRAN

BEGIN

Write your code...

COMMIT TRAN

END

END TRY

BEGIN CATCH

ROLLBACK TRAN
SET @strERROR = 'Error Code: '+CONVERT(VARCHAR(20),ERROR_NUMBER()) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE()
SET @SPFLAG = 'N';
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_NAME',
@RECIPIENTS= 'EMAIL_ADDRESS1;EMAIL_ADDRESS2',
                 @SUBJECT='Error_Message',
@BODY = @strERROR,
@body_format = 'HTML' ;
END CATCH