none
How do I capture @@ROWCOUNT from a stored procedure?

    Question

  • Greetings,

     

    I have a stored procedure that does updates.  The sproc is called from an “Exec SQL Task”.  The sproc returns the number of rows that were updated using this technique:

     

    RETURN @@ROWCOUNT

     

    How do I capture the sproc’s return value (number of rows) in a package-level variable?  I eventually want to log this value.  The sproc is not returning a result set.  I’m new to SSIS so any general guidance would be appreciated.

     

    Thanks,

    BCB

    Tuesday, December 05, 2006 2:58 PM

All replies

  • I presume you don't have any trouble grabbing this value when you execute outside SSIS?

    In this case then the only extra info you need is how to get info out of the Execute SQL Task. There is a great article here:

    The ExecuteSQL Task
    http://www.sqlis.com/default.aspx?58

     

    that covers the Execute SQL Task in depth.

    -Jamie

     

    Tuesday, December 05, 2006 3:07 PM
    Moderator
  • The simplest thing is to change your RETURN to SELECT @@ROWCOUNT. Then set the sql task to "single row" return and map a variable to parameter 0.

    Tuesday, December 05, 2006 5:51 PM
  • Thanks for your reply.  I found it was easy to capture the @@ROWCOUNT to a variable once I started returning it as a result set rather than the sproc return value.  Thanks for your tip.  For some reason I was never able to capture the @@ROWCOUNT value as the sproc return value.

    BCB

    Wednesday, December 06, 2006 2:37 PM
  •  BlackCatBone wrote:

    Thanks for your reply.  I found it was easy to capture the @@ROWCOUNT to a variable once I started returning it as a result set rather than the sproc return value.  Thanks for your tip.  For some reason I was never able to capture the @@ROWCOUNT value as the sproc return value.

    BCB

     

    How come? Did you try and pass it using an output paremter?

    -Jamie

     

    • Proposed as answer by SSISNewbie345 Wednesday, August 01, 2012 9:19 PM
    Wednesday, December 06, 2006 5:30 PM
    Moderator
  • can u plss explain how you were able to solve this ...i have a similar situation...
    Friday, July 27, 2012 5:46 PM
  • @@rowcount example in SSIS: http://microsoft-ssis.blogspot.com/2011/03/rowcount-for-execute-sql-statement.html

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by SSISNewbie345 Wednesday, August 01, 2012 9:19 PM
    Friday, July 27, 2012 6:20 PM
    Moderator