December 12, 2008 at 7:25 am
Hi there,
I have two while loops... one processing thru what we will call accounts and the second one looping thru rules.
WHILE @intIncrementGroupCount<@intGroupCount
BEGIN
.
.
.
BEGIN TRY
WHILE @intIncrementQueryCount < @intQueryCount
BEGIN
.
.
.
EXEC(@vchDynamicallyGeneratedProcedureName)
SET @intIncrementQueryCount = @intIncrementQueryCount + 1
END
END TRY
BEGIN CATCH
BEGIN
INSERT #tblSummary1(GroupID,ComplianceRuleID,[Rule],Result,HasException)
SELECT@intGroupID
,@intComplianceRuleID
,@vchQueryName
,'Unable to run rule, please check input values.'
,1
END
END CATCH
SET @intIncrementGroupCount = @intIncrementGroupCount + 1
END
So basically I want the loop to continue to process the inner loop when it encounters an failure in trying to execute the stored procedure (bad parameters, non-existant proc, etc). Right now for example we have 1 account and 10 rules and if it fails for whatever reason on the 3rd rule, it will not process the other 7.
I thought I could add a CONTINUE statement within the TRY/CATCH but the code apparently hangs when I try that.
I'd appreciate any suggestion.
thanks,
Chris
EDIT: actually I did come up with a solution but I'm not sure if it's a valid one. In the CATCH block after I log the error message I increment the counter and use a GOTO statement that brings the process back to the BEGIN TRY.
valid or hack?
December 16, 2008 at 7:35 am
anyone?
December 16, 2008 at 10:36 am
I never used TRY/CATCH in SQL, but your suggestion solution makes the TRY/CATCH kinda useless to me. So yes, I think you're hacking your code there. But where did the code exactly hang? Do you see the insert?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 18, 2008 at 3:45 am
Hi
Would it be possible for you to change the procedure/script the you execute int the while loop to do a test and if it would generate an error rather insert values into your temp table?
December 18, 2008 at 8:47 am
Actually it was a 'pound head on keyboard' solution. I needed to put the CATCH/TRY inside the 2nd while loop instead of the while loop inside the CATCH/TRY.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply