none
count of rows returned

    Question

  • Hi

    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.

    Any suggestion?

    Thanks
    Eyal

     

    Friday, May 13, 2011 7:15 PM

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.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 14, 2011 9:17 AM

All replies

  • The suggestion is simple. You need to always select @@RowCount immediately after the statement you want to return record count for. So, your SELECT @@ROWCOUNT must be right after the SELECT statement.

    If you can make that batch to be a stored procedure and you will know the resulting table structure, you may use INSERT INTO #TempTable (...) Execute mySP syntax. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 13, 2011 7:18 PM
  • 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.

    /Erland


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 13, 2011 10:04 PM
  • Hi Erland

     

    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.

     

    Thanks, Eyal

     

    Saturday, May 14, 2011 2:29 AM
  • > 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.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 14, 2011 9:17 AM
  • Very well. Thanks Erland
    Saturday, May 14, 2011 2:19 PM
  • 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.

    This is the most simplest solution I could think of.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    Saturday, May 14, 2011 2:59 PM