Search code examples
sqlsql-serverstored-proceduresnested

In nested stored procedures - how to safely continue run the calling procedure even if the inner procedure fails?

I have one scenario, where I have nested stored procedures. I want my main procedure to continue running even if the called procedure (it just have some logging in a table) didn't run successfully. can you please help. many thanks!

main Stored Procedure

exec testSP -- Inner SP

-- I want the following code of main SP to run even if the inner SP fails or there is sometime issues or anything that makes it to fail

code for the main SP

Solution

  • U can use try-catch inside your main stored procedure (and/or your testSP as well).

    BEGIN TRY
  EXEC testSP
END TRY
BEGIN CATCH
  -- do something when an error occurs
  SELECT ERROR_NUMBER(), ERROR_MESSAGE(), 'testSP'
END CATCH