How do you handle errors in stored procedure?

Question

Hi, What can be done to handle errors in stored procedure? Thanks

Andrew Fenster · Answer

You have lots of choices. You can handle the error inside the stored procedure and then return an error code or message. The way this is done varies depending on the database you are using. Oracle, SQL Server, DB2, etc. each have a different way to handle errors. I prefer not to have the database handle the error. I would rather have the call to the database throw an exception and then catch the exception in the code and handle it there. There are several reasons: (1) You still have to wrap your database call in a try/catch block. After all, the database might be down, the connection string might be invalid, etc. So you're going to be writing code to handle errors anyhow. (2) If you catch the error in your code, you can handle it the same way you handle other types of exceptions. (3) If you catch the error in your code, you can change databases without changing your error handling.

Savita Joshi · Answer

@Andrew, Thanks a lot for answering.

Andrew Fenster · Answer

Even in T-SQL (SQL Server's version of SQL) there are many ways to do things. Here's one: Declare @ErrorCode int Select @ErrorCode = @@Error If @ErrorCode = 0 Begin --Some statement [Update something here.] Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin --Another statement [Insert something here.] Select @ErrorCode = @@Error End Return @ErrorCode This uses the built in @@Error object. If everything succeeded, @@Error should be 0. The code that calls the stored procedure will check the return code to make sure it's 0. A good article: http://msdn.microsoft.com/en-us/library/aa175920%28v=sql.80%29.aspx

Savita Joshi · Answer

@Andrew Fenster, Thanks for replying. You spoke about handling errors inside stored procedure, how do you do that please tell me.In case using SQL 2005

How do you handle errors in stored procedure?

Answers (4)