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?

Answers


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;

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.


Need Your Help

Iterating on each element from an array of arrays on Postgresql

sql arrays postgresql plpgsql postgresql-8.3

I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:

How can I deserialize a JSON string in Mono?

c# .net json mono

How can I deserialize a JSON string in C# (Mono)?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.