Unanswered

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)

Suggested answer

Michael E. Gernaey 53,345 Super User 2025 Season 2 on at

Like (1)

Report

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
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