December 15, 2009 at 2:26 pm
I know this is odd, but is there any sort of "success" block when you run a try?
BEGIN TRY
--T-SQL
END TRY
BEGIN CATCH
--Catch code
END CATCH
BEGIN SUCCESS
--it worked
END SUCCESS
December 15, 2009 at 4:01 pm
Firstly, wrong place to post, the topic Try Catch block is not available in SQL Server 2000.
I have not heard/read anything such as TRY SUCCESS in SQL Server.
BEGIN TRY block is there to write your code if there is not exception / errors which means, the code placed in Begin Try and End Try will run when when no errors are encountered.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 8:26 am
Oh thanks!
For 2000 I suppose I could just convert all T-SQL into an nvarchar @Sql and do this:
execute @SqlSuccess = sp_executesql @Sql
if (@SqlSuccess = 0)
begin
--success code
end
else
begin
--fail code
end
Probably the best option?
December 16, 2009 at 10:06 am
This is one of the old ways of doing things:
DECLARE @Errorint
--YourCode
SET @Error = @@ERROR
IF @Error = 0
BEGIN
--Success Code
END
ELSE
BEGIN
--Fail Code
END
It is very important that you set @Error after every single statement because @@ERROR changes after each one executes. I usually use something like this for making sure every statement in the batch executed successfully:
SET @Error = CASE WHEN @Error = 0 THEN @@ERROR ELSE @Error END
For example, even this would be bad logic:
--YourCode
If @@ERROR <> 0
SET @Error = @@ERROR
The problem with this is that the IF statement executed successfully and the new value of @@ERROR is 0 instead of whatever it was before.
December 16, 2009 at 10:09 am
The "success" block IS the try block. If something succeeds in the try block, it just moves on to the next command.
- 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
December 16, 2009 at 2:24 pm
Garadin,
Wow, that looks great. Thanks!
GSquared,
Haven't had a chance to try that out on 2005 yet, but will the try block stop executing even if it's an error that would be skipped/bypassed on a non-try block (i.e. non-sudden death type errors where code continues)?
December 16, 2009 at 2:31 pm
Take a look at Try Catch in Books Online. It has all the details.
Basically, errors with a severity below 11 won't fire off the Catch block. Errors 11 and above will, with the exception that errors that would kill the connection (above 20) will do that instead.
There's more to it, but it's pretty straight-forward.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply