Find if Stored Procedure Error Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch?

We have a lot of legacy code 200+ stored procedures. I have seen @Output method and try catch, however we would have to refactor legacy code, which may break. Just curious, if there is a way to catch error without changing old code.

declare @TestErrorFlag bit -- or can be int or varchar(255)

-- trying to do something similar to this
set @TestErrorFlag  = if error(exec dbo.testprocedure)

Trying to store in some type of indicator variable

@TestErrorFlag = 1 if procedure succeeds, 
@TestErrorFlag = 0 if procedure fails
If an error condition in your stored procedure raises an exception, your only option without changing the code inside the procedure, really, is to wrap the call in try/catch:

BEGIN TRY;
  EXEC sys.sp_helpdb @foo = 1;
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;


If your procedure returns an error number without bubbling up an exception, e.g.:

CREATE PROCEDURE dbo.bob
  @fail bit = 0
AS
BEGIN
  IF (@fail = 1)
  BEGIN
    RETURN -1;
  END
  -- unnecessary, but:
  ELSE
  BEGIN
    RETURN 0;
  END
END


Then you can use this format:

DECLARE @TestErrorFlag int;
EXEC @TestErrorFlag= dbo.bob @fail = 1;
PRINT @TestErrorFlag;


Of course in this case if any error condition in the procedure does lead to an exception, the PRINT won't happen.



Error handling is a massive topic. I would browse around these articles:


Error and Transaction Handling in SQL Server - Part 1
Error and Transaction Handling in SQL Server - Part 2
Error and Transaction Handling in SQL Server - Part 3