Try Success

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Try Success

Try Success

Rate Topic

Display Mode

Topic Options

Author

Message

saivko

Posted Tuesday, December 15, 2009 2:26 PM

SSC 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

Bru Medishetty

Posted Tuesday, December 15, 2009 4:01 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, 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

saivko

Posted Wednesday, December 16, 2009 8:26 AM

SSC 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

Garadin

Posted Wednesday, December 16, 2009 10:06 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation

Post #835287

GSquared

Posted Wednesday, December 16, 2009 10:09 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

saivko

Posted Wednesday, December 16, 2009 2:24 PM

SSC 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

GSquared

Posted Wednesday, December 16, 2009 2:31 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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 »

Permissions