Get RETURN value from Proc

Question

I have a SQL Server procedure which returns a result set, and is working well. I am adding error handling (TRY/CATCH), and in the event of an error, want to RETURN -1 to indicate an issue (Or another negative integer to maybe know the reason for the failure).

Is there a way to get the return value (As well as the result set, if required) from the procedure call?

I call the procedure like this:

Context.project_scheduled_event_payments(st.id);

In this case, I don't get a result set, as I don't need one. But I would like to know if the proc returned with NULL (All went fine), or a negative number indicating an issue:

IF(@EndDate IS NOT NULL)
              BEGIN
            BEGIN TRY
                UPDATE scheduled_event_transaction 
                    SET Deleted = GETUTCDATE(),
                        lastupdateuser = 0, 
                        lastupdatedate = GETUTCDATE() 
                WHERE Scheduled_Event_ID = @scheduled_event_id 
                AND scheduled_payment_date > @EndDate
            END TRY
            BEGIN CATCH
                ROLLBACK
                RETURN -1
            END CATCH
        END
            RETURN -- Success

You might want to check this check this http://stackoverflow.com/questions/14810037/get-return-value-from-stored-procedure-in-asp-net — Umesh, Jun 09 '14 at 05:44

score 1 · Accepted Answer · edited May 23 '17 at 12:20

As suggested by SystemOnline, refer get-return-value-from-stored-procedure-in-asp-net.

But I will suggest to throw error & catch in C#/vb code like:

BEGIN TRY
 ...

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        --@ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               16, --@ErrorSeverity,-- Severity.
               @ErrorState -- State.
               );
END CATCH;

Please note that I have explicitly set severity to 16 so that error is thrown to code.

Get RETURN value from Proc

1 Answers1