SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Try Success


Try Success

Author
Message
saivko
saivko
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 141
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

Bru Medishetty
Bru Medishetty
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5656 Visits: 1950
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.


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
saivko
saivko
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 141
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?
Garadin
Garadin
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6873 Visits: 4107
This is one of the old ways of doing things:

DECLARE @Error            int

--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.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56693 Visits: 9730
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
saivko
saivko
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 141
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)?
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56693 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search