What is the best way of determining whether our own Stored procedure has been executed successfully or not.

Question

Hi I know some ways that we can use in order to determine that whether our own Stored procedure has been executed successfully or not. (using output parameter, putting a select such as select 1 at the end of the stored procedure if it has been executed without any error, ...)

so which one is better and why?

Answer 1

Having a print statement that clearly states whether the SP has been created or not would be more readable.

e.g.

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
...
...
...
GO
IF OBJECT_ID('dbo.CustOrdersDetail') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.CustOrdersDetail >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CustOrdersDetail >>>'
GO

Answer 2

Using RAISERROR in case of error in the procedure integrates better with most clients than using fake out parameters. They simply call the procedure and the RAISERROR translates into an exception in the client application, and exceptions are hard to avoid by the application code, they have to be caught and dealt with.

Answer 3

SP is very much like a method/subroutine/procedure & they all have a task to complete. The task could be as simple as computing & returning a result or could be just a simple manipulation to a record in a table. Depending on the task, you could either return a out value indicating the result of the task whether it was a success, failure or the actual results.

Answer 4

If you need common T-SQL solution for your entire project/database, you can use the output parameter for all procedures. But RAISEERROR is the way to handle errors in your client code, not T-SQL.

Answer 5

Why don't use different return values which then can be handled in code?

Answer 6

Introducing an extra output paramter or an extra select is unnecessary.

If the only thing you need to know is whether there is a problem, a successful execution is good enough choice. Have a look at the discussions of XACT_ABORT and TRY...CATCH here and here.

If you want to know specific error, return code is the right way to pass this information to the caller.

Answer 7

In the majority of production scenarios I tend to deploy a custom error reporting component within the database tier, as part of the solution. Nothing fancy, just a handful of log tables and a few of stored procedures that manage the error logging process.

All stored procedure code that is executed on a production server is then encapsulated using the TRY-CATCH-BLOCK feature available within SQL Server 2005 and above.

This means that in the unlikely event that a given stored procedures were to fail, the details of the error that occurred and the stored procedure that generated it are recorded to a log table. A simple stored procedure call is made from within the CATCH BLOCK in order to record the relevant details.

The foundations for this implementation are actually explained in books online here

Should you wish, you can easily extend this implementation further, for example by incorporating email notification to a DBA or even an SMS alert could be sent dependent on the severity of the error.

An implementation of this sort ensures that if your stored procedure did not report failure then it was of course successful.

Once you have a simple and robust framework in place, it is then straightforward to duplicate and rollout your base implementation to other production servers/application platforms.

Nothing special here, just simple error logging and reporting that works.

If on the other hand you also need to record the successful execution of stored procedures then again, a similar solution can be devised that incorporates log table/s.

I think this question is screaming out for a blog post……..