title: SQL Server/ Azure SQL - For JSON
body: <p>I am writing a number of SQL Procedures that return JSON to my application using the for Json option on select queries. Some of the queries are lookups into the database - these need to return the row count back to the application so it can handle paging.</p>
  <p>I would like to return my json as something like this</p>
  <pre class="lang-json prettyprint-override"><code>{
      &quot;TotalRows&quot;: 5122,
      &quot;Requisitions&quot;: 
          [{          
              &quot;ID&quot;: &quot;4D4FFB67-7EE2-EC11-B656-28187815440C&quot;,
              &quot;Ref&quot;: &quot;SC000014676&quot;,
              &quot;JobTitle&quot;: &quot;Bid Management Lead &quot;,
              &quot;ShortlistDueDate&quot;: &quot;2022-01-16T00:00:00Z&quot;,
              &quot;WrkDaysToShortlistDeadline&quot;: -137,
              &quot;NoRequiredForShortlist&quot;: 15
          }, {
  </code></pre>
  <p>I am calculating the total rows the query returns into a variable @TotalRows. I have tried to move the total rows in this query out to another select in the nest; but I cant get sql to compile the proc; it complains about column 1 being missing. This sample code stripped of where clauses etc; returns results - but with the total rows reported in each element.</p>
  <p>Is there a way to do it in a single select that i'm missing - or should i cut my losses and put the output into a variable and use a second query to populate the total rows?</p>
  <p><strong>Current response</strong></p>
  <pre class="lang-json prettyprint-override"><code>{
      &quot;Requisitions&quot;: [{
              &quot;TotalRows&quot;: 5122,
              &quot;ID&quot;: &quot;4D4FFB67-7EE2-EC11-B656-28187815440C&quot;,
              &quot;Ref&quot;: &quot;SC000014676&quot;,
              &quot;JobTitle&quot;: &quot;Bid Management Lead &quot;,
              &quot;ShortlistDueDate&quot;: &quot;2022-01-16T00:00:00Z&quot;,
              &quot;WrkDaysToShortlistDeadline&quot;: -137,
              &quot;NoRequiredForShortlist&quot;: 15
          },
  </code></pre>
  <p>**Example of current logic **</p>
  <pre class="lang-sql prettyprint-override"><code>Declare @TotalRows Int
  
  
      select 
      @TotalRows = count(*) 
  
      from 
          dbo.tblRequisitions aa
  
  set dateformat dmy
  select
  
  (
  select 
      @TotalRows as TotalRows
      
      , ID 
      ,Ref 
      ,JobTitle 
      ,ShortlistDueDate 
      ,WrkDaysToShortlistDeadline 
      ,NoRequiredForShortlist 
  
      
      
  from 
      (
          select 
            aa.RequisitionID  as ID
          , aa.RequisitionRef as Ref
          , aa.RequisitionJobTitle as JobTitle
          , dateadd(dd,14, aa.RequisitonAdded) as ShortlistDueDate
          , datediff(dw, getdate(), dateadd(dd,14, RequisitonAdded)) as WrkDaysToShortlistDeadline
          , aa.RequisitonNoPositionsTotal * 5 as NoRequiredForShortlist
  
  
          from 
              dbo.tblRequisitions aa
  
          order by 
          
              aa.RequisitonAdded
  
              offset (@Page * @noRows) rows
              fetch next @noRows rows only
      ) requisitions
  order by 
      TotalRows, 
      requisitions.id
  for 
      json auto 
      , Root ('Requisitions')
      , INCLUDE_NULL_VALUES
  ) as 
      Requisitions
  </code></pre>