Can i tweak the response from StoredProcedure returning JSON

REST API
1

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.

2

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…

3

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.

4

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

5

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?

6

Hi Pit,

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

7

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”: “”
}