SQL Server stored procedure try Catch TRANSACTION (GO)

Source: Internet
Author: User
Tags exception handling getdate try catch

CREATE PROCEDURE Yourprocedure
As
BEGIN
SET NOCOUNT on;

Begin TRY---------------------Start catching exceptions
BEIN TRAN------------------Start a transaction
UPDATE A SET a.names = b.names from table 1 as A INNER JOIN table 2 as B on a.id = b.ID

UPDATE A SET a.names = b.names from table 1 as A INNER JOIN table 2 as B on a.test = B.test

Commit TRAN-------COMMIT Transaction
End TRY-----------Ending catch exception
BEGIN catch------------An exception is caught
If @ @TRANCOUNT > 0---------------Determine if there is a transaction
BEGIN
ROLLBACK TRAN----------ROLLBACK TRANSACTION
END
EXEC yourlogerrorprocedure-----------Execute stored procedure to record error information in a table
End CATCH--------Ending exception handling
END

---------------------------------------------Records stored procedures that manipulate the wrong information--------------------------------------------

CREATE PROCEDURE Yourlogerrorprocedure
@ErrorLogID [int] = 0 OUTPUT-Contains the errorlogid of the row inserted
As--by uspLogError in the errorlog table
BEGIN
SET NOCOUNT on;

--Output parameter value of 0 indicates that error
--Information was not logged
SET @ErrorLogID = 0;

BEGIN TRY
--Return If there is no error information to log
IF Error_number () is NULL
RETURN;

--Return If inside an uncommittable transaction.
--Data insertion/modification is no allowed when
--a transaction is in the uncommittable state.
IF xact_state () =-1
BEGIN
PRINT ' cannot log error since the current transaction was in a uncommittable state. ‘
+ ' Rollback the transaction before executing uspLogError in order to successfully log error information. ';
RETURN;
END

INSERT [dbo]. [Operateerrorlog]
(
[Operatename],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[Errorprocedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT (sysname, current_user),
Error_number (),
Error_severity (),
Error_state (),
Error_procedure (),
Error_line (),
Error_message ()
);
SET @ErrorLogID = @ @IDENTITY;
END TRY
BEGIN CATCH
PRINT ' An error occurred in stored procedure usplogerror: ';
EXECUTE yourprinterrorprocedure;-----------------stored procedure for printing error messages
RETURN-1;
END CATCH
END;

CREATE PROCEDURE Yourprinterrorprocedure
As
BEGIN
SET NOCOUNT on;

   -Print error information. 
    print ' ERROR ' + CONVERT (varchar (50), Error_number ()) +
          ', Severity ' + CONVERT (varchar (5), ERROR _severity ()) +
          ', state ' + CONVERT (varchar (5), error_state ()) + 
          ', Procedure ' + ISNULL (error_procedure (), '-') +&NBSP
          ', line ' + CONVERT (varchar (5), Error_line ());
    PRINT error_message ();
END;

CREATE TABLE [dbo]. [Errorlog] (
[Errorlogid] [INT] IDENTITY (*) is not NULL,
[Errortime] [DateTime] Not NULL CONSTRAINT [Df_errorlog_errortime] DEFAULT (getdate ()),
[UserName] [sysname] COLLATE chinese_prc_ci_as not NULL,
[ErrorNumber] [INT] Not NULL,
[ErrorSeverity] [INT] Null
[ErrorState] [INT] Null
[Errorprocedure] [nvarchar] (126) COLLATE chinese_prc_ci_as NULL,
[ErrorLine] [INT] Null
[ErrorMessage] [nvarchar] (4000) COLLATE chinese_prc_ci_as not NULL,
CONSTRAINT [Pk_errorlog_errorlogid] PRIMARY KEY CLUSTERED
(
[Errorlogid] Asc
) with (Ignore_dup_key = OFF) on [PRIMARY]
) on [PRIMARY]

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/WeiZhang_son_Ding/archive/2010/02/05/5291732.aspx

Http://www.cnblogs.com/BpLoveGcy/archive/2010/03/22/1691407.html

  1. ALTER PROC usp_accounttransaction
  2. @AccountNum INT,
  3. @Amount DECIMAL
  4. As
  5. BEGIN
  6. BEGIN try --start the try Block.
  7. BEGIN TRANSACTION -Start the TRANSACTION.
  8. UPDATE mychecking SET Amount = amount-@Amount
  9. WHERE AccountNum = @AccountNum
  10. UPDATE mysavings SET Amount = Amount + @Amount
  11. WHERE AccountNum = @AccountNum
  12. COMMIT TRAN --Transaction success!
  13. END TRY
  14. BEGIN CATCH
  15. IF @ @TRANCOUNT > 0
  16. ROLLBACK TRAN --rollback in case of Error
  17. --Can Raise ERROR with RaiseError () Statement including the details of the exception
  18. --raiserror (Error_message (), Error_severity (), 1)

DECLARE @ErrorMessage NVARCHAR (4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = Error_message (),
@ErrorSeverity = Error_severity (),
@ErrorState = Error_state ();

--use RAISERROR inside the CATCH block to return error
--Information about the original error that caused
--execution to the CATCH block.
RAISERROR (@ErrorMessage,--Message text.
@ErrorSeverity,--Severity.
@ErrorState-State.
);

    1. END CATCH
    2. END
    3. GO

  1. BEGIN TRY
  2. SELECT GETDATE ()
  3. SELECT 1/0--evergreen divide by zero example!
  4. END TRY
  5. BEGIN CATCH
  6. SELECT ' There is an error! ' + error_message ()
  7. RETURN
  8. END CATCH;

2. The function table that obtains the error message:

The following system functions are valid in the CATCH block. Can be used to get more error messages:

function Description
Error_number () Returns the error number of the error message that caused the CATCH block to run.
Error_severity () Returns the severity level of the error message that caused the CATCH block to run
Error_state () Returns the status number of the error message that caused the CATCH block to run
Error_procedure () Returns the name of the stored procedure in which the error occurred
Error_line () Returns the line number where the error occurred
Error_message () Returns the full text of the error message that caused the CATCH block to run

    • BEGIN TRY
    • Try Statement 1
    • Try Statement 2
    • ...
    • Try Statement M
    • END TRY
    • BEGIN CATCH
    • Catch Statement 1
    • Catch Statement 2
    • ...
    • Catch Statement N
    • END CATCH

SQL Server stored procedure try Catch TRANSACTION (GO)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.