Creating a logic app to retrieve data from SQL server in JSON format.
Calling SQL Stored Proc from Logic App trigged via HTTP Request.
And using below select Query - FOR JSON to create the output as JSON and using ResultSet in the response of Logic App.
SELECT ID, NAME FROM #TABLENAME FOR JSON PATH, ROOT('Data')
But I'm getting the ResultSet along with Table and column name created by SProc select statement. Which was along with output from Stored Proc and included in the ResultSet of SProc.
Does anyone know a method to remove this highlighted output from result set in Stored Proc output?
Output Expected -
{"Pulse Data": [{"A":"cc", "B":"ZZ"},{"A":"dd", "B":"YY"}, {"A":"ee", "B":"XX"}]}
CodePudding user response：
I had reproduced the same in my local environment and got succeeded.
By doing this below approach in Azure Logic Apps, and achieved the required result:
Here is the excepted result:
CodePudding user response：
I had the same issue yesterday. I was able to resolve this by wrapping the query in to an output variable in the stored procedure, then using the output parameters in the logic app.
PROCEDURE [dbo].[Something_to_JSON] @JSON NVARCHAR(MAX) OUTPUT
SELECT @JSON = ( SELECT DISTINCT something, something
CodePudding user response：
Hope this helps, or you can email me @ [email protected]
