Can i tweak the response from StoredProcedure returning JSON

Hi,
I have setup the my API using DreamFactory. Thanks for that. However, I am struggling to get the response in particular form. The problem is like below:

  1. Suppose i have 1 to Many relationship maintained in SQL server. I have Office Table and Address table. Where 1 office can have multiple addresses, So Office_id is acting as ForeignKey in Address Table. While running the query stated below
    SELECT O.*, A.FIRST_LINE_1 as Address from OFFICE AS O
    INNER JOIN ADDRESS AS A ON A.OFFICE_ID= O.OFFICE_ID
    where a.office_id=@OFFICE_ID
    I getting two set of records in JSON format as below: n that are correct
    [{
    “OFFICE_ID”:“2”,“OFFICE_NAME”:“OFFICE USA”,“OFFICE_STRENGTH”:“300”,“OFFICE_COUNTRY”:“2”,“OFFICE_CITY”:“3”,“OFFICE_CREATED_BY”:“2015-09-02”,“OFFICE_UPDATE_BY”:null,“Address”:"First Line for office 2 "},
    {
    “OFFICE_ID”:“2”,“OFFICE_NAME”:“OFFICE USA”,“OFFICE_STRENGTH”:“300”,“OFFICE_COUNTRY”:“2”,“OFFICE_CITY”:“3”,“OFFICE_CREATED_BY”:“2015-09-02”,“OFFICE_UPDATE_BY”:null,“Address”:"First Line for office 2 "
    }]

This is correct set of data i am getting. however i want this of appear as below:
Where Address fields appears as Collection of Office. Something like below

[
{
“OFFICE_ID”: “2”,
“OFFICE_NAME”: “OFFICE USA”,
“OFFICE_STRENGTH”: “300”,
“OFFICE_COUNTRY”: “2”,
“OFFICE_CITY”: “3”,
“OFFICE_CREATED_BY”: “2015-09-02”,
“OFFICE_UPDATE_BY”: null,
ADDRESS [{
{
“FIRST_LINE_1”: “First Line for office”
},
{
“FIRST_LINE_1”: “First Line for office2”
}
]}}]

Is it possible? Guide me how to get the result as a collection of collection form of JSON from StoredProcedure.

Did you try with output parameters?
As you already use a stored procedure why not do the “whole work” in procedure and just print out the final results with Output Parameters?

First query for office where office = ? , second query for adress where office = ?, then try to “style” second query with output parameters…

It is difficult to return a set of records as Out parameter from SP? I think as per MSDN site it is not possible… If you can help out in returning a set of records as Out Parameter from SP, it will be great.

Example written above in my query statement will have multiple records for “Address” field. How to return this as a OUT parameter from SP. I am using SQLServer2008/2012/2014. Please suggest how to use Schema and Wrapper values in the request to tweak the result set from stored-procedure

What I’ve managed to receive is this one:

[
[
{
“OFFICE_ID”: “1”,
“OFFICE_NAME”: “Demo Office”,
“OFFICE_STRENGTH”: “100”
}
],
[
{
“FIRST_LINE_1”: “Sample 1”
},
{
“FIRST_LINE_1”: “Sample 2”
},
{
“FIRST_LINE_1”: “Sample 3”
}
]
]

Not so beautiful but perhaps a solution?

Hi Pit,

Is it possible for you to share the solution. I may try and bring some more beauty to it.

Hi yash

this is procedure; quick + dirty :wink:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspGetOffice]
@OFFICE_ID int = NULL
AS
BEGIN

SET NOCOUNT ON;

SELECT [OFFICE].[OFFICE_ID]
  ,[OFFICE_NAME]
  ,[OFFICE_STRENGTH]

FROM [dbo].[OFFICE]

WHERE [OFFICE].[OFFICE_ID] =@OFFICE_ID
(SELECT
[FIRST_LINE_1]
FROM [dbo].[OFFICE]
JOIN ADRESS on OFFICE.OFFICE_ID = ADRESS.OFFICE_ID
WHERE [OFFICE].[OFFICE_ID] =@OFFICE_ID)
END

The body in DF will be sth like that:

{
“params”: [
{
“name”: “OFFICE_ID”,
“param_type”: “”,
“value”: “1”,
“type”: “”,
“length”: 0
}
],
“schema”: {
field_name”: “”
},
“wrapper”: “”,
“returns”: “”
}