June 1, 2010 at 1:08 pm
Hello,
In development or in debugging I try to identify what went wrong and return messages that are useful and mean something. I THOUGHT I could have multiple Try Catch blocks in my Sp's but it seems when the first errors code outside the subsequent blocks executes but NOT the subsequent Try / Catch blocks for example:
DECLARE @AcctNbr AS VARCHAR(9)
SET @AcctNbr = '026983970'
DECLARE @ErrCode AS BIT
DECLARE @AcctNum AS DECIMAL(13)
DECLARE @DivAcctNum AS DECIMAL(13)
DECLARE @DecAcctNum AS DECIMAL(11,2)
BEGIN TRY
SET @AcctNum = CONVERT(DECIMAL(13) ,@AcctNbr)
END TRY
BEGIN CATCH
SET @ErrCode = 1
SELECT @ErrCode As 'Round 1'
END CATCH
SELECT @AcctNum As 'AcctNum'
BEGIN TRY
--Why do we divide by 100??? I have no idea
SET @DivAcctNum = @AcctNum / 100
END TRY
BEGIN CATCH
SET @ErrCode = 1
END CATCH
SELECT @DivAcctNum As 'DivAcctNum'
BEGIN TRY
SET @DecAcctNum = CONVERT(decimal(11 ,2), @DivAcctNum)
END TRY
BEGIN CATCH
SET @ErrCode = 1
END CATCH
SELECT @DecAcctNum AS 'Final'
RETURNS:
AcctNum
---------------------------------------
26983970
(1 row(s) affected)
DivAcctNum
---------------------------------------
269840
(1 row(s) affected)
Final
---------------------------------------
269840.00
(1 row(s) affected)
Which is what I would expect but when we induce an error (SET @AcctNbr = '0269%*83970') only the first executes resulting in:
Round 1
-------
1
(1 row(s) affected)
AcctNum
---------------------------------------
NULL
(1 row(s) affected)
DivAcctNum
---------------------------------------
NULL
(1 row(s) affected)
Final
---------------------------------------
NULL
(1 row(s) affected)
As Brutal as it was at least I could evaluate different segments with @@ERROR after the line to watch???
TIA
JB
June 1, 2010 at 6:30 pm
June 2, 2010 at 6:44 am
I think you've misunderstood how your code works. . .
This is your code, with some comments: -
-- Declare Variables
DECLARE @AcctNbr AS VARCHAR(9)
SET @AcctNbr = '026983970'
DECLARE @ErrCode AS BIT
DECLARE @AcctNum AS DECIMAL(13)
DECLARE @DivAcctNum AS DECIMAL(13)
DECLARE @DecAcctNum AS DECIMAL(11, 2)
--First Try Block
BEGIN TRY
SET @AcctNum = CONVERT(DECIMAL(13), @AcctNbr)
END TRY
--If that fails, set the ErrCode to 1 and Display ErrorCode
BEGIN CATCH
SET @ErrCode = 1
SELECT @ErrCode AS 'Round 1'
END CATCH
--Display AccountNumber
SELECT @AcctNum AS 'AcctNum'
--Second Try Block
BEGIN TRY
SET @DivAcctNum = @AcctNum / 100
END TRY
--If that fails, set the ErrCode to 1
BEGIN CATCH
SET @ErrCode = 1
END CATCH
--Display DivAcctNum -> If the try failed, this will display 'NULL' as
--it hasn't been set. If the previous Try failed, it will also be 'NULL', since
--@AcctNum is NULL so you're doing 'Null' / 100.
SELECT @DivAcctNum AS 'DivAcctNum'
--Third Try Block
BEGIN TRY
SET @DecAcctNum = CONVERT(DECIMAL(11, 2), @DivAcctNum)
END TRY
--If that fails, set the ErrCode to 1
BEGIN CATCH
SET @ErrCode = 1
END CATCH
--Display DecAcctNum -> If the try failed, this will display 'NULL' as
--it hasn't been set. If the previous Try failed, it will also be 'NULL', since
--@DivAcctNum is NULL so you're converting 'Null'.
SELECT @DecAcctNum AS 'Final'
Below is a "proof of concept" for the use of multiple Try-Catch blocks for meaningful errors.
DECLARE @VAR1 INT,
@VAR2 INT,
@VAR3 INT,
@RETURN INT,
@ERROR BIT
SET @VAR1 = 0
SET @VAR2 = 0
SET @VAR3 = 0
BEGIN TRY
SET @RETURN = 1 / @VAR1
SELECT @RETURN AS 'Return 1'
END TRY
BEGIN CATCH
SET @ERROR = 1
SELECT @ERROR AS 'Error 1'
END CATCH
BEGIN TRY
SET @RETURN = 2 / @VAR2
SELECT @RETURN AS 'Return 2'
END TRY
BEGIN CATCH
SET @ERROR = 1
SELECT @ERROR AS 'Error 2'
END CATCH
BEGIN TRY
SET @RETURN = 3/ @VAR3
SELECT @RETURN AS 'Return 3'
END TRY
BEGIN CATCH
SET @ERROR = 1
SELECT @ERROR AS 'Error 3'
END CATCH
June 2, 2010 at 7:43 am
I understand your points regarding where I had indeed misread my own code. But the code snippet I meant to post was this one where I don't seem to be going into the second Try / Catch regardless of the Null / 100 issue???
DECLARE @AcctNbr AS VARCHAR(9)
SET @AcctNbr = '0269%*83970'
DECLARE @ErrCode AS BIT
DECLARE @AcctNum AS DECIMAL(13)
DECLARE @DivAcctNum AS DECIMAL(13)
DECLARE @DecAcctNum AS DECIMAL(11,2)
BEGIN TRY
SET @AcctNum = CONVERT(DECIMAL(13) ,@AcctNbr)
END TRY
BEGIN CATCH
SET @ErrCode = 1
SELECT @ErrCode As 'Round 1'
END CATCH
SELECT @AcctNum As 'AcctNum'
BEGIN TRY
--Why do we divide by 100??? I have no idea
SET @DivAcctNum = @AcctNum / 100
END TRY
BEGIN CATCH
SET @ErrCode = 2
SELECT @ErrCode As 'Round 2'
END CATCH
SELECT @DivAcctNum As 'DivAcctNum'
BEGIN TRY
SET @DecAcctNum = CONVERT(decimal(11 ,2), @DivAcctNum)
END TRY
BEGIN CATCH
SET @ErrCode = 3
SELECT @ErrCode As 'Round 3'
END CATCH
SELECT @DecAcctNum AS 'Final'
Which Returns:
Round 1
-------
1
(1 row(s) affected)
AcctNum
---------------------------------------
NULL
(1 row(s) affected)
DivAcctNum
---------------------------------------
NULL
(1 row(s) affected)
Final
---------------------------------------
NULL
(1 row(s) affected)
Obviously I am probably still misunderstanding but shouldn't my second and third catch selects execute?
June 2, 2010 at 7:57 am
Your code behaves exactly as it is written.
It returns only one error as it is one error happens.
Do you expect your code to stop after the firts error?
Then you need to have only one try catch block. Something like this:
DECLARE @AcctNbr AS VARCHAR(9)
SET @AcctNbr = '0269%*83970'
DECLARE @ErrCode AS BIT
DECLARE @AcctNum AS DECIMAL(13)
DECLARE @DivAcctNum AS DECIMAL(13)
DECLARE @DecAcctNum AS DECIMAL(11,2)
BEGIN TRY
SET @ErrCode = 1
SET @AcctNum = CONVERT(DECIMAL(13) ,@AcctNbr)
SELECT @AcctNum As 'AcctNum'
SET @ErrCode = 2
--Why do we divide by 100??? I have no idea
SET @DivAcctNum = @AcctNum / 100
SELECT @DivAcctNum As 'DivAcctNum'
SELECT @DecAcctNum AS 'Final'
SET @ErrCode = 3
SET @DecAcctNum = CONVERT(decimal(11 ,2), @DivAcctNum)
END TRY
BEGIN CATCH
DECLARE @sql varchar(1000)
SET @sql = 'SELECT ' + CAST(@ErrCode as VARCHAR) + ' As [Round ' + CAST(@ErrCode as VARCHAR) + ']'
EXECUTE (@sql)
END CATCH
June 2, 2010 at 9:52 am
I expect it to go from one Try Catch to the next like this:
BEGIN TRY
SET @AcctNum = CONVERT(DECIMAL(13) ,@AcctNbr)
END TRY
BEGIN CATCH
SET @ErrCode = 1
SELECT @ErrCode As 'Round 1'
END CATCH
SELECT @AcctNum As 'AcctNum'
Then I expect it to enter the next Try Catch, error, and display the "Round 2" select
BEGIN TRY
--Why do we divide by 100??? I have no idea
SET @DivAcctNum = @AcctNum / 100
END TRY
BEGIN CATCH
SET @ErrCode = 2
SELECT @ErrCode As 'Round 2'
END CATCH
SELECT @DivAcctNum As 'DivAcctNum'
June 2, 2010 at 10:25 am
Look, your code does enter the next (second) "try" block! But why you expect "error, and display the "Round 2" select"? There is no error to catch in this block that why is nothing is showing up.
The second "try" block performs just @AcctNum / 100. At this point, the value of AcctNum is null and it is perfectly fine, in SQL, to devide NULL value by 100 and get NULL as result - NO ERROR!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply