# Can i tweak the response from StoredProcedure returning JSON **URL:** \ **Category:** REST API\ **Created:** [September 8, 2015, 2:24pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453 "2015-09-08T14:24:57Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![yash\_dec](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@yash\_dec](https://community.dreamfactory.com/u/yash_dec)\ **Post date:** [September 8, 2015, 2:24pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/1 "2015-09-08T14:24:57Z")
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. ---
**Author:** ![pitw](https://avatars.discourse-cdn.com/v4/letter/p/b5e925/32.png) [@pitw](https://community.dreamfactory.com/u/pitw)\ **Post date:** [September 8, 2015, 4:11pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/2 "2015-09-08T16:11:35Z")
Did you try with [output parameters](https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Stored-Procedures)? 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… ---
**Author:** ![yash\_dec](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@yash\_dec](https://community.dreamfactory.com/u/yash_dec)\ **Post date:** [September 9, 2015, 12:31pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/3 "2015-09-09T12:31:55Z")
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. ---
**Author:** ![yash\_dec](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@yash\_dec](https://community.dreamfactory.com/u/yash_dec)\ **Post date:** [September 9, 2015, 2:37pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/4 "2015-09-09T14:37:12Z")
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 ---
**Author:** ![pitw](https://avatars.discourse-cdn.com/v4/letter/p/b5e925/32.png) [@pitw](https://community.dreamfactory.com/u/pitw)\ **Post date:** [September 9, 2015, 7:13pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/5 "2015-09-09T19:13:33Z")
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? ---
**Author:** ![yash\_dec](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@yash\_dec](https://community.dreamfactory.com/u/yash_dec)\ **Post date:** [September 10, 2015, 11:52am UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/6 "2015-09-10T11:52:46Z")
Hi Pit, Is it possible for you to share the solution. I may try and bring some more beauty to it. ---
**Author:** ![pitw](https://avatars.discourse-cdn.com/v4/letter/p/b5e925/32.png) [@pitw](https://community.dreamfactory.com/u/pitw)\ **Post date:** [September 10, 2015, 3:04pm UTC](https://community.dreamfactory.com/t/can-i-tweak-the-response-from-storedprocedure-returning-json/1453/7 "2015-09-10T15:04:40Z")
Hi yash this is procedure; quick + dirty 😉 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”: “” }