1

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.

I would like to return my json as something like this

{
    "TotalRows": 5122,
    "Requisitions": 
        [{          
            "ID": "4D4FFB67-7EE2-EC11-B656-28187815440C",
            "Ref": "SC000014676",
            "JobTitle": "Bid Management Lead ",
            "ShortlistDueDate": "2022-01-16T00:00:00Z",
            "WrkDaysToShortlistDeadline": -137,
            "NoRequiredForShortlist": 15
        }, {

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.

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?

Current response

{
    "Requisitions": [{
            "TotalRows": 5122,
            "ID": "4D4FFB67-7EE2-EC11-B656-28187815440C",
            "Ref": "SC000014676",
            "JobTitle": "Bid Management Lead ",
            "ShortlistDueDate": "2022-01-16T00:00:00Z",
            "WrkDaysToShortlistDeadline": -137,
            "NoRequiredForShortlist": 15
        },

**Example of current logic **

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
u07ch
  • 13,324
  • 5
  • 42
  • 48

1 Answers1

0

Instead of using ROOT, nest the whole thing in a subquery, and do the SELECT COUNT(*) in another subquery, then use another FOR JSON over all of that.

select
  (
  select 
    count(*) 
  from 
    dbo.tblRequisitions aa
  ) as TotalRows,
    
  (
  select 
    , 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 
    requisitions.id
  for 
    json auto 
    , INCLUDE_NULL_VALUES
) as 
    Requisitions

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
Charlieface
  • 52,284
  • 6
  • 19
  • 43