SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TRY CATCH - best way to indicate no error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thebrenda
Starting Member

22 Posts

Posted - 09/04/2013 :  09:22:10  Show Profile  Reply with Quote
I have a TRY CATCH. What is the best way to know that there was not an error. The way our messaging works, i have to write out a second message that says either "Failed" or "Success". The "Failed" will be in the CATCH. But after the CATCH I need to know that there was not an error and the CATCH was not executed. Obviously I can set a variable, just wondering if there is a more streamline approach.

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 09/04/2013 :  09:24:55  Show Profile  Reply with Quote
There is always the function

RAISERROR


djj
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 09/04/2013 :  10:09:31  Show Profile  Reply with Quote
How would I use RAISEERROR (now THROW) to know that an error did not occur?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/04/2013 :  10:22:46  Show Profile  Reply with Quote
RAISERROR or THROW is used when you do want to report an error. I wouldn't use it for indicating a no-error condition. If you need a "Success" message, write that as the last message in the TRY block.
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 09/04/2013 :  10:27:57  Show Profile  Reply with Quote
Thank you for the obvious answer. Just put the 'Sucess' as the last line in the TRY block.

How do you mark a question answered?
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 09/04/2013 :  10:36:10  Show Profile  Reply with Quote
I think the best practice is that at the end of your Catch, you return the errorcode: Return ERROR_NUMBER(). What you are saying is you want to continue execution code after you encounter a Catch, I don't think that is a good idea.

And end every stored procedure with: Return 0. That way if you call an sp, you'll know by the return value if it errored out or not.

But to answer your question, I think the only way to know after the Catch is to set a variable as you mentioned.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
269 Posts

Posted - 09/04/2013 :  12:12:20  Show Profile  Reply with Quote
BEGIN TRY
...
...
PRINT 'Success'
END TRY
BEGIN CATCH
...
...
PRINT 'Failed'
END CATCH
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4352 Posts

Posted - 09/04/2013 :  12:19:06  Show Profile  Reply with Quote
quote:
Originally posted by James K

RAISERROR or THROW is used when you do want to report an error. I wouldn't use it for indicating a no-error condition. If you need a "Success" message, write that as the last message in the TRY block.

I don't disagree that it shouldn’t be used, but people do use RAISERROR with a severity of 11 or lower to pass messages back to the caller. As severity 11 or lower will not cause execution to transfer to the catch bock, thus providing a way to bubble up an informational/warning message.

So, the OP, could use that method to get a message back to the calling environment. But, in all reality, we have much richer error handling then we had in the past, so there is no need to return messages and check return codes anymore.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000