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.
Advertisement
Answer
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()
.