Return Two Result Sets When Using With Tempresults As

I have the following query where I would like to return two result sets. One being the results as a table and the second just returning the number of potential results, or MaxResul

Solution 1:

This can't be done (as far as I'm aware). There's three workarounds, one which allows you to keep as two distinct result sets, and the other two which require merging the results into the same result set (as either an extra row or an extra column).

1. Instead of a CTE, push the results into a temporary table then query from that.
2. Merge the count into the actual result set as another row: use a UNION ALL and give the count row suitable values for ID, Name and RowID so you can extract it from the rest of the data
3. Add the SELECT COUNT(*) into the primary result set as an extra column with a CROSS JOIN or similar.

For this latter, you can do it by changing your primary query to:

SELECTId, Name, RowId, countTable.totalRowsFROMResultsTempCROSSJOIN (SELECT COUNT(*) AS totalRows FROM ResultsTemp) AScountTableGROUPBYId, Name, RowIdHAVINGRowIdBETWEEN @StartRowand (@StartRow + @MaxRows);

I can't vouch for the performance - you'd need to profile.

Solution 2:

Createtable #temp
(
    Id int, 
    Name Varchar(100), 
    RowId int
)

DECLARE@StartRowint;
DECLARE@MaxRowsint;
set@StartRow=0;
set@MaxRows=5;

SELECTROW_NUMBER() OVER (ORDERBY FTS.RANK DESC) AS RowId,
Id, Name into #temp FROM tNews
INNERJOIN CONTAINSTABLE(tNews, *, 'FORMSOF(INFLECTIONAL, hello)') 
AS FTS ON tNews.Id = FTS.[KEY]

SELECT Id, Name, RowId FROM #temp
GroupBy Id, Name, RowId 
Having RowId between@StartRowand (@StartRow+@MaxRows);

selectCOUNT(RowId) from #temp;