title: Use SQL to return a JSON string
body: <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>