0

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
  • 1
    if error(exec sp) is the same as try exec sp catch. My SP returns 0 if everything went well and other value, usually a negative value, if went wrong. – McNets Nov 28 '18 at 19:26
  • So like this, declare @@testvalue int set @@testvalue = iferror (exec dbo.testprocedure), had to use two ampersands, so it would not ping user – user162241 Nov 28 '18 at 19:32
  • 1
    When your stored procedures have errors, do you raise exceptions? RETURN <int>; where <int> != 0, or...? – Aaron Bertrand Nov 28 '18 at 19:42
4

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:

  • What fist line? Copy what result? I’m not sure I understand which code sample you’re talking about. – Aaron Bertrand Nov 29 '18 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy