Databases
StackOverflow2013
Postsdbo
POUse SQL to return a JSON string
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUse SQL to return a JSON string
    primarykey
    data
    text
    <p>This is a "best practice" question. We are having internal discussions on this topic and want to get input from a wider audience.</p> <p>I need to store my data in a traditional <code>MS SQL Server</code> table with normal columns and rows. I sometimes need to return a <code>DataTable</code> to my web application, and other times I need to return a <code>JSON</code> string. </p> <p>Currently, I return the table to the middle layer and parse it into a <code>JSON</code> string. This seems to work well for the most part, but does occasionally take a while on large datasets (parsing the data, not returning the table). </p> <p>I am considering revising the stored procedures to selectively return a <code>DataTable</code> or a <code>JSON</code> string. I would simply add a <code>@isJson bit</code> parameter to the SP.</p> <p>If the user wanted the string instead of the table the SP would execute a query like this:</p> <pre><code>DECLARE @result varchar(MAX) SELECT @result = COALESCE(@results ',', '') + '{id:"' + colId + '",name:"' + colName + '"}' FROM MyTable SELECT @result </code></pre> <p>This produces something like the following:</p> <pre><code>{id:"1342",name:"row1"},{id:"3424",name:"row2"} </code></pre> <p>Of course, the user can also get the table by passing false to the @isJson parameter.</p> <p>I want to be clear that the data storage isn't affected, nor are any of the existing views and other processes. This is a change to ONLY the results of some stored procedures.</p> <p>My questions are:</p> <ol> <li>Has anyone tried this in a large application? If so, what was the result?</li> <li>What issues have you seen/would you expect with this approach? </li> <li>Is there a better faster way to go from table to JSON in SQL Server other than modifying the stored procedure in this way or parsing the string in the middle tier?</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USErikE
    1. USdavids
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUse SQL to return a JSON string
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUse SQL to return a JSON string
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUse SQL to return a JSON string
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTell us more about the middle layer. What is taking it so long? Is it compiled code? It should be very possible to write an application that turns a recordset into JSON and has quite adequate performance.
      singulars
      1. POUse SQL to return a JSON string
      1. USErikE
    2. COAssuming you escape your special characters properly, this is doable... but... why would you? SQL Server is a terrible string parsing engine, and you have a built-in XML engine that can always spit out compliant XML in a relatively efficient manner.
      singulars
      1. POUse SQL to return a JSON string
      1. USJeremy Holovacs
    3. CO@ErikE Most tables convert to json very quickly. This only arose because we are parsing a table for FullCalendar. The middle parse simply takes the table and uses a foreach to cycle through fields and rows to build the string. We can probably fix some things in the middle to get it to perform faster, but if we can avoid an extra step in the process by having SQL do it...why not?
      singulars
      1. POUse SQL to return a JSON string
      1. USdavids
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload