Return of a Stored Procedure

Question

I have an X-Action on my app that when performing it, a stored Procedure in the bank that generates a insert in a table. So far quiet.

What I need is a way that when this stored Procedure is executed return me what was executed and whether error or not.

Ex: I have 5 actions, when executing them I need a return informing what worked and what gave error to be shown in a grid for the User.

Answer 1

You can use the Try-catch inside the sp:

BEGIN TRY  
    SELECT 1/0;  
END TRY  
    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;

If you want to force an error depending on the logic of the sp, use the RAISERROR

If you are using an older version of SQL Server, you can open a transaction and commit or rollback according to a condition or error.

DECLARE @E INT
BEGIN TRAN  
    SELECT 1/0;  

    SET @E = @@ERROR

    SELECT @E

IF @E > 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN