Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Try Success Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 2:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 1:10 PM
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
Post #834758
Posted Tuesday, December 15, 2009 4:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
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
Post #834788
Posted Wednesday, December 16, 2009 8:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 1:10 PM
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?
Post #835129
Posted Wednesday, December 16, 2009 10:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:28 AM
Points: 1,519, Visits: 4,076
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
Post #835287
Posted Wednesday, December 16, 2009 10:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #835290
Posted Wednesday, December 16, 2009 2:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 1:10 PM
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)?
Post #835447
Posted Wednesday, December 16, 2009 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #835452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse