Post reply

@@Rowcount for multiple statements

  • August 23, 2010 at 2:12 am

    #100298

    When executing the following statement:

    sp_executesql N'select * from table1; select * from table2 go select * from table3'

    If i were then to exec SELECT @@ROWCOUNT it would only return the number of rows from the last select statement (table3).

    Other than parsing the string for all statement and batch terminators and then executing each statement and @@ROWCOUNT is there another way that i can retrieve the number of rows. For example the Query> Messages tab shows the number of rows affected by each stmt?

  • August 23, 2010 at 3:50 am

    #1210321

    @@ROWCOUNT will return the affected rows based on the statement before @@ROWCOUNT.

    You don't have to look for batch terminator.

    Other than that, yes, you'd have to look for each statement where you'd like to know the number of affected rows. But instead of running each statement separately you could assign the result to separate variables and select those at the very end of your batch:

    sp_executesql N'DECLARE @t int

    DECLARE @t2 int

    SELECT * from table1

    SELECT @t=@@ROWCOUNT

    SELECT * from table2

    SELECT @t2=@@ROWCOUNT

    SELECT @t,@t2'


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 23, 2010 at 3:53 am

    #1210322

    Thanks for your reply LutzM.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply