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