February 17, 2016 at 2:13 pm
I have a huge stored procedure A that runs nightly and calls a bunch of stored procedure within it.
If the inner stored procedure should fail its fine as the inner sp are working on different/independent area, the A sp should continue running the other stored procedure.
How can I achieve this ?
Also ensuring the error is returned for each inner store procedure if failure does happen .
I started by creating a ErrorLog Table and ErrorHandling Sp
Create Procedure A as
DECLARE @sql NVARCHAR(MAX)
BEGIN TRY
SET @sql = EXEC ChildSp 1
EXECUTE sp_executesql @sql
END TRY
BEGIN CATCH
EXEC ErrorHandling
END CATCH
BEGIN TRY
SET @sql = EXEC ChildSp 2 @emp_id
EXECUTE sp_executesql @sql
END TRY
BEGIN CATCH
EXEC ErrorHandling
END CATCH
February 17, 2016 at 2:39 pm
Based on what you've given us:
1) Your procedure is missing quotes.
2) Why are you using dynamic SQL when your queries are static?
If that isn't the issue, I suggest that you post your actual code instead of pseudo code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 17, 2016 at 3:51 pm
Make sure the sub-procs have their own CATCH blocks. You can use empty/dummy blocks if you don't care about errors there:
CREATE PROCEDURE sub_proc
AS
...
BEGIN CATCH
--ignore error here
END CATCH
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply