I moved a stored procedure from one database to another and that went fine. I then went to run it and got these errors:

Msg 208, Level 16, State 1, Procedure dbo.myStoredProc, Line 159 [Batch Start Line 2]
Invalid object name ‘missingTable’.

Msg 266, Level 16, State 2, Procedure dbo.myStoredProc, Line 159 [Batch Start Line 2]
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

The problem turned out to be that one of the tables was missing, but I want to know if there is a way for my SP to pass up the first error through an output variable. Below is the general structure of my SP. I use the @Status variable to pass up either ‘OK’ (if there are no issues) or an error message. In the example below, the table myTable doesn’t exist. When I go into SSMS and right click > Execute Stored Procedure, it returns the errors above and shows that @Status = null and Return Value = null.

create procedure myStoredProc
  @InputParm nvarchar(100)
  , @Status nvarchar(4000) output
as
begin

  set nocount on

  set @Status = 'OK'

  begin try

    --...compute stuff

    begin tran

      delete from myTable

      insert into myTable select 1

    commit tran

  end try

  begin catch

    if xact_state() <> 0 rollback tran
    set @Status = (select error_message())

  end catch

end