How to rollback a SQL Server transaction and use output param? 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.
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 &gt; 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.