Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Try Success


Try Success

Author
Message
saivko
saivko
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
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