July 20, 2008 at 8:16 pm
Hello All,
While using try catch I noticed something that I didn't think was occuring. When executing a try/catch block, data from within the try is being returned when an error occurs.
Normally when using a try/catch in other coding languages, any data in the try/catch block which is successful is not returned only the catch data is.
Is there a way that I can prevent the successful data from being returned if one part of the try/catch fails or is this the only way that the SQL implementation can operate?
Example:
USE AdventureWorks;
GO
BEGIN TRY
-- Non error result
SELECT 'Bacon' as BaconFirst
-- Error result
SELECT 1/0 AS BaconSecond
-- Non error result
SELECT 'Bacon' as BaconThird
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO
In the example, "BaconFirst" is returned successfully and "BaconSecond" is returned as an empty result set and "BaconThird" is not returned at all. Ideally what I want is not to have "BaconFirst" and "BaconSecond" returned at all if any of them errored.
Thanks
Anubis.
July 21, 2008 at 4:32 am
on line help states
A TRY…CATCH construct cannot span multiple batches.
also
A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.
===
===
as half a thought, try the following
GO
BEGIN TRY
-- Non error result
SELECT 'Bacon' as BaconFirst
select @@rowcount as '@@rowcount', @@error as error, ERROR_NUMBER() as 'ERROR_NUMBER()'
-- Error result
SELECT 1/0 AS BaconSecond
select @@rowcount as '@@rowcount', @@error as error, ERROR_NUMBER() as 'ERROR_NUMBER()'
-- Non error result
SELECT 'Bacon' as BaconThird
select @@rowcount as '@@rowcount', @@error as error, ERROR_NUMBER() as 'ERROR_NUMBER()'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO
===
===
the above will jump into the try catch at the point of encountering an error
===
===
if you could figure a way to programatically validate the selects using an exist statement, then you could call a raiseerror command
and this would allow you to validate all selects prior to trying to execute them
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
to jump into the catch block:D
July 21, 2008 at 4:51 am
In most other programming languages, you would see the same result. Anything that happens successfully before the error within a try/catch will actually happen unless you explicitly manage the process.
What you may want to do in your situation is collect all of the data in temp tables or table variables and select them all at the end of the process only if @@rowcount is greater than zero for all of your statements.
July 21, 2008 at 10:09 am
Insert all of your data into temp tables. Return them at the end of the Try block. You won't reach that if you end up in the Catch block.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2008 at 5:23 pm
Hi All,
Thank you for your comments. It appears that I will have to alter my code as what I was attempting to do was catch errors on a more global scale instead of a more granulated one as what it appears this must be used for.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply