1

I am trying to set an output param in a stored procedure, but I think that when I rollback the transaction I think I'm also rolling back the assignment to @out. Is that correct?

If so, how can I return a message and rollback a transaction? I am fetching the @out param from C#.

create PROCEDURE [dbo].[sp]
@out varchar(2000) output
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        SET @OUT = "success";
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        set @out = 'not success';
        ROLLBACK TRANSACTION
    END CATCH
END

I was originally doing a

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;

...but that didn't help, though I would prefer that method.

7

A more robust way to write this stored procedure would be:

CREATE PROCEDURE dbo.sp
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT 1/0; -- An error!

        /* Other good code omitted*/

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        DECLARE @Message nvarchar(2048) = ERROR_MESSAGE();
        DECLARE @Severity integer = ERROR_SEVERITY();
        DECLARE @State integer = ERROR_STATE();

        RAISERROR(@Message, @Severity, @State);
        RETURN -1;
    END CATCH;
END;

Note the use of XACT_ABORT and nothing of consequence before the BEGIN TRY. Also RAISERROR is often preferred over THROW for reasons covered in the reference below, but importantly:

In difference to RAISERROR, ;THROW always aborts the batch.

Aborting the batch results in any output parameters not being assigned.

Error handling in SQL Server is quirky in the extreme, so I would encourage you to review the seminal work on the topic by Erland Sommarskog: Error and Transaction Handling in SQL Server.

  • What's the use of the TRY/CATCH construct AND XACT_ABORT together? Wouldn't the TRY/CATCH nullify any cases where XACT_ABORT would usually abort the transaction? – George.Palacios Jun 5 '18 at 10:34
2

Since you're using SQL Server 2016, I would recommend using THROW

This will take the error that caused the TRY construct to divert to the CATCH construct, and throw that same error again.

If you want here, you could also log the error to a table, do any cleanup exercises that the ROLLBACK won't do, etc.

To be clear, to the calling application, this will be an exception and not a result set.

Note: The statement prior to the THROW must be terminated with a semicolon, otherwise it may be interpreted as e.g. ROLLBACK TRANSACTION THROW with THROW as the transaction or savepoint name.

  • Question - when I use throw <err n0.>, "msg", <some other no.>. It says err no. must be > 5000 < some very large number. But the ERROR_NUMBER() function returns a small value. This seems completely useless to me... am i missing something? – Zach Smith Jun 5 '18 at 12:39
  • @ZachSmith if you want to re-throw the same error just use THROW; in your catch block. If you supply an error number it must be in the custom error number rage of >= 50000. – David Browne - Microsoft Jun 6 '18 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.