Answered

Get a ReturnCode in SQL Stored Procedures

(1) Share

Report

Posted on by FG-11091158-0

I have created this SQL Stored Procedure

CREATE PROCEDURE [dbo].[sp_RegistrarAccesoInvitacionNoNumerada]
    @IdEvento INT,
    @Puerta INT
AS
BEGIN   
	BEGIN TRY
        INSERT INTO CONTROLACCESOEVENTOS_REGISTROINVITACIONESNONUMERADAS
        VALUES (@IdEvento, GETDATE(), @Puerta);
		RETURN 0;  -- Success;
    END TRY
    BEGIN CATCH
        RETURN -1; -- Error
    END CATCH
END

I invoke it from PowerApps with this code:

UpdateContext({
    varRegistroResultadoProcedimientoAlmacenado:
    sp_InvitacionesNoNumeradas.dbospRegistrarAccesoInvitacionNoNumerada({
        IdEvento:varEventID,
        Puerta:varIdPuerta
    })
});

UpdateContext({
    varResultadoProcedimientoAlmacenado: 
    varRegistroResultadoProcedimientoAlmacenado.ReturnCode
});

The procedure executes well. The values are inserted in the database, but I don't get any ReturnCode. This is my 'varRegistroResultadoProcedimientoAlmacenado' record:

No OutputParameters (ok), no ResultSets (ok, too), but... no ReturnCode? It should be 0 or -1

Can you tell me how can I check the execution of my Stored Procdeure?

Thanks! :)

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Suggested answer

Michael E. Gernaey 53,963 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi

Please check this out

Power Apps Guide - SQL - How to return data from Stored Procedures to Power Apps - Power Apps Guide - Blog

Was this reply helpful? Yes No
Verified answer

FG-11091158-0 37 on at

Like (0)

Report
Copy link

Link copied!
I tried EVERYTHING (ReturnCode, OutputParameters...) and, at the end, the only solution I found was to get the 'ReturnCode' as a ResultSet:

ALTER PROCEDURE [dbo].[sp_RegistrarAccesoInvitacionNoNumerada] @IdEvento INT, @Puerta INT, @Resultado INT OUTPUT AS BEGIN BEGIN TRY INSERT INTO CONTROLACCESOEVENTOS_REGISTROINVITACIONESNONUMERADAS VALUES (@IdEvento, GETDATE(), @Puerta); -- Return 0 (success) RETURN 0; -- as ReturnCode; SET @Resultado = 0; -- as OutputParameters SELECT 0 AS Res; -- as ResultSet END TRY BEGIN CATCH -- Return -1 (error) RETURN -1; -- as ReturnCode; SET @Resultado = -1; -- as OutputParameters SELECT -1 AS Res; -- as ResultSet END CATCH END

Called the procedure:

UpdateContext({ var: sp_InvitacionesNoNumeradas.dbospRegistrarAccesoInvitacionNoNumerada({ IdEvento: varEventID, Puerta:varIdPuerta }) })

And read the results:

UpdateContext({ ReturnCode_Try1: var.ReturnCode, // Got Nothing ReturnCode_Try2: var.OutputParameters.Resultado, //Got Nothing ReturnCode_Try3: Value(First(var.ResultSets.Table1).Res) //Ok!!! })

Only ReturnCode_Try3 gave me what I neded

Hope it helps anyone with the same problem :)

NOTE:

As stated in the post recommended by Michael:

Power Apps Guide - SQL - How to return data from Stored Procedures to Power Apps - Power Apps Guide - Blog

You can get MULTIPLE resultsets. So, if your stored procedure returs a ResultSet with data, you can send the 'returncode' in a second table.

Was this reply helpful? Yes No