@@Rowcount for multiple statements

• scott.radden

  SSC-Addicted

  Points: 425

  More actions

  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?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  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]

• scott.radden

  SSC-Addicted

  Points: 425

  More actions

  August 23, 2010 at 3:53 am

  #1210322

  Thanks for your reply LutzM.