TSQL输出行数

Question

I have a stored procedure that is outputting my results in XML format; all is good with the output.

I need to however include the row count it is outputting if possible along with it.

 SELECT A.[id],
             A.[petName],
             A.[petCaption],
             B.[petType],
             C.[FirstName] as ownerFirstName,
             C.[LastName] as ownerLastName,
             A.[imageName],

             (
                SELECT CONVERT(varchar(20),sum(transactionAmount), 1) as totalRaised
                FROM petContestTransactionsDonations
                WHERE submissionID = A.[id]
                FOR    XML PATH ('transactionDetails'), TYPE, ELEMENTS
             )
      FROM petContestSubmissions as A
      LEFT OUTER JOIN petContestTypes as B
      ON A.[petType] = B.[id]
      LEFT OUTER JOIN EmpTable as C
      ON A.[empID] = C.EmpID
      LEFT OUTER JOIN petContestTransactionsEntries as E
      ON E.[submissionID] = A.[id]
      WHERE E.[transactionStatus] = 'completed'
     ORDER BY A.[id] 
        OFFSET  @offset ROWS 
        FETCH NEXT @rows ROWS ONLY 
      FOR    XML PATH ('submission'), TYPE, ELEMENTS, ROOT ('root');

I am passing offset and rows to the stored procedure as I am using it with pagination. It is getting records for that page limiting to X rows.

Even though I say get me the next 10 records, it may only have 8 left. That's what I need to return; the total records it found in the select statement.

Is it best to do that in the stored procedure or in my php that is looping over the records?

Answer 1

If you want the row count on each row, then include:

count(*) over () as RowCount

in the outermost select clause.

If you want to read all the data into the application first, then do it in the application layer.

EDIT:

If you want the number of rows returned by the query, then you might as well do it in the application layer. You can also do:

select t.*, count(*) over () as RowCOunt
from (<your query here>) t;