TSQL output the row count
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?
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.
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;
Maybe this will meet your needs. Not in the same query, but you can try
SELECT @@ROWCOUNT AS RowsAffected
immediately after your query, you should get what you are looking for. @@ROWCOUNT is per session, so as long as you don't end your connection, should be ok.