CODEWITHSUNDEEP
.netsql-servert-sql
Ivan Mihov
Ivan Mihov

Reputation: 33

T-SQL Throw Exception within a transaction

Today I was trying to write a transaction, within a stored procedure that in certain returns different numbers. It is necessary the C# code to know what exactly was the error. The problem is, that if the transaction is not completed it throws the same exception.

I could not throw exception inside the transaction, always was giving wrong syntax. I was trying with this pattern: 

THROW 51000, 'The record does not exist.', 1;

Is it possible to make something like this:

BEGIN TRAN
  TRY

  IF...
     THROW ERROR 4;

  IF..
    THROW ERROR 2;

COMPLETE TRAN;
CATCH
   RETURN ERROR NUMBER;

I just need the specific number of the error returned from the stored procedure.

Can you please give me an example of that?

P.S. I forgot which is the version of our sql server, no I'm not in the office. It is either 2008 or 2012.

Upvotes: 1

Views: 9083

Answers (2)

Ionic
Ionic

Reputation: 3935

You should use RAISERROR (read more in the documentation) instead of THROW (see documentation).

THROW will only re-throw an existing exception inside a CATCH block instead triggering a complete new exception. It was introduced in SQL Server 2012. The main usage for two functions which throw an error is quite simple. If an error occur inside a TRY, and you re-throw the error in your CATCH block using RAISERROR - as it was state of the art in 2008 - it may provide many useful information but the codeline in which the error occurred is shifted to the line where you raised the error using RAISERROR, not in the line where the error really occurred. Using THROW it will re-throw the error with all information, even the original line in which the error originally occurred.

Instead you can throw an own exception using this:

RAISERROR(N'Your Errormessage',16,1)

By the way, each error message used by RAISERROR will have a errormessage of 50000 or above. You can define your own error messages with their own id's (above 50000). If you defined your own message id, you can use RAISERROR to throw your user defined error like this:

RAISERROR(50001,16,1)

Side info: You can overload RAISERROR to parse values into your error message.

RAISERROR(N'A error occurred while iterating over row ID %i',16,1,@rowId)

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can use the following syntax:

BEGIN TRY
     BEGIN TRANSACTION
          IF ...  RAISERROR(51001, 16, 1)
          IF ...  RAISERROR(51002, 16, 1)
     COMMIT TRANSACTION
END TRY
BEGIN CATCH
     DECLARE @ErrorNumber INT = ERROR_NUMBER();
     DECLARE @ErrorLine INT = ERROR_LINE();
     DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
     DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
     DECLARE @ErrorState INT = ERROR_STATE();

     IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION;

     RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Error number should be greater then 50000. You can add those with sp_addmessage procedure. Throw was introduced in Sql Server 2012. There are some differences. You can easily find them in MSDN.

Upvotes: 4

Related Questions