Microsoft Flows Stored Procedure

[Anonymous]

Hi Guys,

I had initially inquired about how to captured response from a custom connector which connects to an API. I have managed to do-away with that and at the last step of my configuration, i am using a stored procedure call before an "Update Sql Row" connector.

The problem is the Update Action automatically looks to update the "Row Id" as well, which i think is a drawback. So i have tried a work-around where i am using a stored procedure from my On-premise DB to read the total count of rows and increment with 1, and use that as a value for RowID in the next step i.e. in the Update Row action. However, when testing the output of my SP in Management Studio, i am able to get the appropriate value, whereas when the Flow executes it, the "Output Parameters" as well as the "Result Set" comes to be null, which is breaking my Flow during execution. Any ideas why this might be happening?

Help will be much appreciated. 

[SameerCh · Accepted solution]

Hi You are hitting a current limitation of the SQL Connector when executing stored procedures for on-premise SQL Server. You can find some of these limitations documented here (#4 in the list): https://docs.microsoft.com/en-us/connectors/sql/ An update row can be used to update an existing row. The Row ID corresponds to the primary key column for the table you are planning to update. If you plan to insert a row, you should be using the "Insert row" instead. You can use an Identity column to automatically get a primary key that increments on the server side. Sameer

[Anonymous · Accepted solution]

There is a way to extract data from the stored procedure. It does not give an Output parameter value or a response as such, but in my SP, i had defined a select command at the end of the procedure which will allow us to access the result in the "Result Parameter" field of the Stored Procedure during execution. This should be treated as an array of object when the fields are to be accessed in the corresponding steps. This way we can receive the desired result from the stored procedure.