Find if Stored Procedure Error

Question

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

Answer 1

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