dan dan - 4 months ago 8
SQL Question

How can I get count of one select during selecting in SQL Server

I have a big select with many inner join and left join and select to select like:

SELECT
( ... )
ORDER BY
Price


The question is for count of select am I must to run this select again ?

SELECT
COUNT( ... )
ORDER BY
Price


Is there any easy way to run one times and get result of select and count of select ?

Here is my C# code with Entity Framework:

string strQuery = "....";

IQueryable<ProductDto> list = _entities.Database.SqlQuery<ProductDto>(strQuery).AsQueryable();

Answer

You can use select @@rowcount to get the number of rows selected by the previous statement as a second resultset, or use SET @myOutputVar = @@ROWCOUNT it as an output parameter of your stored procedure.

A few Q/A's here on StackOverflow that may also help