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.