CODEWITHSUNDEEP
sql-server
Alan2
Alan2

Reputation: 24572

How should I specify ERROR return arguments in a stored procedure

I have my stored procedure:

CREATE PROCEDURE [dbo].[get_tests]
    @ErrorLine INT OUTPUT,
    @ErrorMessage NVARCHAR(1000) OUTPUT,
    @ErrorProcedure NVARCHAR(100) OUTPUT,
    @ErrorNumber INT OUTPUT
AS
    SET NOCOUNT ON

    //
    BEGIN CATCH
        SET @ErrorLine = ERROR_LINE()
        SET @ErrorMessage = ERROR_MESSAGE()
        SET @ErrorProcedure = ERROR_PROCEDURE()
        SET @ErrorNumber = ERROR_NUMBER()
        RETURN
    END CATCH

Can someone advise me is this a good way to declare the error information? Any additional advice or suggestions would be much appreciated

Upvotes: 1

Views: 32

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172528

Your way of handling the errors is good to proceed with. You can also have a look at MSDN: Using TRY...CATCH in Transact-SQL:

  • If the stored procedure does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.
  • If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the
    stored procedure.

The other way is to use the select statement in your CATCH block to select the errors like:

BEGIN CATCH
SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

Upvotes: 1

Related Questions