How to Get Count(*) and table data based on where condition in single query-CodePudding

I've a stored procedure like this:

create procedure sp_testsp
@param1 nvarchar(800),
@count bigint output
as 
begin 

select * from tbl_test tt where tt.col1 = @param1;

set @count = select Count(*) from tbl_test tt where tt.col1 = @param1;

end

In the above ex I'll be getting the table data and the Count of total rows based on where in two queries. This is working fine, but in my actual case the select query is large and calling it twice, once for table and second for count like above is costing time.

Just wondering if there any efficient way of doing this that I don't know of and possible in a single query instead of calling the same one twice?

I've tried in a bit different way which is also working but that still involves calling the query twice and the time for executing is same:

Select * ,
(select count(*) from tbl_test tt where tt.col1 = @param1) as TotalCount
from tbl_test tt where tt.col1 = @param1;

Any help is appreciated, thanks.

CodePudding user response：

You can use

SELECT @@ROWCOUNT;

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

You can find out more at https://docs.microsoft.com/vi-vn/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-2017

CodePudding user response：

If you want to return the count in the same recordset without returning multiple recordsets you can use count() with over()

For example

select *, count(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

or if the results are larger than an int as suggested by your output data type,

select *, count_big(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

Also as mentioned, if you want to keep a second result set you can assign the count to your output variable using @@rowcount or rowcount_big().

CodePudding user response：

You can achieve your goal using COUNT() and OVER(). Please check the below query and don't forget to change where condtion. You can also check it in demo link: http://sqlfiddle.com/#!18/d9c68/5

SELECT *, COUNT(*) OVER() as TotalRow
FROM tbl_test
WHERE Name LIKE '%Name%'