t-sql Return Error Codes vs RaiseError

Question

Hi I am writing a stored procedure that will be executing a batch of jobs on an hourly schedule and I am trying to decide whether to return errors or raise them. Assuming I would be logging the errors inside each job which would lead to better performance and maintainability?

e.g.

--With Error Codes

CREATE PROCEDURE Job1
AS
BEGIN

    BEGIN TRY
        --Do some work
    END TRY
    BEGIN CATCH
        --log error
        RETURN 1; --Return 1 for error
    END CATCH
    RETURN 0;

END

CREATE PROCEDURE USP_BatchJob
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @Error INT;
    EXEC @Error = Job1;
    IF @Error <> 0
        GOTO ErrorHandling;

    EXEC @Error = Job1;
    IF @Error <> 0
        GOTO ErrorHandling;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

    RETURN;

ErrorHandling:
    IF @@TRANCOUNT > 0
        ROLLBACK;
END

e.g. RaiseError

CREATE PROCEDURE Job1
AS
BEGIN

    BEGIN TRY
        --Do some work
    END TRY
    BEGIN CATCH
        --log error
        RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
    END CATCH

END

CREATE PROCEDURE USP_BatchJob
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY

    EXEC Job1;

    EXEC Job1;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;
    END CATCH

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

END

The latter seems to produce more maintainable code

Answer 1

Being about coding style, this may be a religious issue. The main difference between a return code and an exception is that the exception will continue to be passed up the chain of calls.

In general, in the code that I write, I use return values to return the status of a stored procedure as an "application error". As with your example, 0 means success and anything else means failure. Whenever I call a stored procedure in real code, I have checks on the return value as well as any new errors that might arise. By the way, in SQL Server, you cannot check for failure with "@retval <> 0", because stored procedures can return NULL.

Of course, exceptions/database errors can still occur. The idea is that when an exception is recognized, it gets logged and handled. The system "error" turns into an application error.

The one issue I have encountered is the interaction with SQL Server Agent. For this, you want to raise an error to "fork" to the error handling step. This is easily done within a job step, by looking at the return value and then generating an error.