Answered by:

count of rows returned

Question

I am trying to write tsql code that will execute a batch (received as a parameter) and count the number of rows returned. I can't change the batch (sp or adhoc) text.
Using select @@ROWCOUNT after executing the code does not work all the time. For example,

consider the following example:

create table t3 (a int)
select count(*) from t1
drop table t3
go

Obiously, the batch returns one row for the select count. However, because the last operation is drop table, the @@ROWCOUNT will return 0.

Answers

> Think of this as a tool that takes queries from sys.dm_exec_query_stats , rerun them (for example in test environment) and record the number of rows returned. I used this tool as a simple way to create workload.

I see. In that case, I think the CLR approach that I mentioned is the best solution, if you want to keep it in SQL Server. If you are more comfortable with, say, VBscript, you could run it that way. But a pure T-SQL solution that always works is out of reach,
I', afraid.

This is will not work out. @@rowcount always returns the number of rows affect by the most recently executed statement.

You could work around this with INSERT EXEC as Naomi suggested, but then you need to know the shape of the result set in advance. There is a horrible trick with OPENQUERY that permits you use SELECT INTO, but OPENQUERY easily fails as well.

I think the best bet is to write a CLR procedure that runs the query, and the count the rows comes back on the reader object.

Then again, if you tell us why you want to do this in the first place, we may have a better suggestion for your root problem.

As I explained, I can not change the batch that I dynamically execute.
So Naomi suggestion will not work.

Think of this as a tool that takes queries from sys.dm_exec_query_stats , rerun them (for example in test environment) and record the number of rows returned.
I used this tool as a simple way to create workload.

> Think of this as a tool that takes queries from sys.dm_exec_query_stats , rerun them (for example in test environment) and record the number of rows returned. I used this tool as a simple way to create workload.

I see. In that case, I think the CLR approach that I mentioned is the best solution, if you want to keep it in SQL Server. If you are more comfortable with, say, VBscript, you could run it that way. But a pure T-SQL solution that always works is out of reach,
I', afraid.

One possible solution could be to count the records @ front-end. For e.g., if you are using .net as your front-end language, then you can just execute the batch either as a RAW query from within your code or pass it to an SP....ultimately just count the
rows returned in your record set in your front end code and you are done.