How to get rowcount

Hi,

How to get the row count for a particular query being executed within a session and scope. is there any option to get the value being displayed in the messages tab when a query is executed (no of rows affected). is there anything equivalent to scope_identity for getting the identity value inserted in a session and scope.

Vivek S

to obtain number of rows affected...

@.@.RowCount (return an int)

RowCount_Big() (return a big int)

to obtain last genereted identiy value...

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

The scope of the @.@.IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

|||did this help?

How to get Recordset count

I'm currently using SQL 2005 but in previous versions I remember that within Query Analyzer there was a recordset count that displayed in the lower right hand corner. Currently within 2005 I can't find a way to do this without doing it manually which I can't do now since the stored procedure I'm testing has almost 100 recordsets. Any help would be greatly appricated.

Jerry:

That feature is still available; check and see if you are displaying the "status bar" (I think). In the meantime I'll do some more checking about that feature.

Kent

|||

In SSMS, it should be visible in the status bar of the Results window.

Welcome as a new user to SQL Server Jerry. Hopefully, you will be able to get the help you need from these forums, and as your skills progress, please don't forget to drop in occasionally and help others. (You will ALWAYS know something someone else can benefit from...)

It's not a 'recordset' -the output from a SQL query is referred to as a 'resultset'.

|||

My mistake on the 'recordset' comment. I know it's a resultset.

I've been using SQL for a bit now but the result set count still doesn't show up. I have all the bars visible. I see "Ln", "Col", "Ch" & I see the number of rows returned and server i'm running against under the resultset window.

|||

I think that perhaps you are looking to get the message that "n row(s) affected" that may follow an activity. (It sounds like perhaps you are seeing the "n rows" in the status bar.)

To get the "n row(s) affected" message, use this command:

SET NOCOUNT OFF

To stop getting the "n row(s) affected" message, use this command:

SET NOCOUNT ON

|||I thought in previous versions of SQL it actually said how man result sets were coming back. Not the number of rows but the number of result sets. I need 97 result sets to come back with a series of stored procedures that are being executed. I don't want to go through and count every result set.|||

I'm not aware of any facility within T-SQL that reports the number of resultsets returned. One query = one resultset.

I think that may be available with some of the ADO objects, specifically the datareader object.

I supose you could add a counter variable in the procedure, and check each query for @.@.ROWCOUNT, incremented as desired, and then return a statement using that variable.

|||

I don't remember one either.

You could possibly return the data to the grid and then (assuming no errors or print statements) you could get a count from the

(1 row(s) affected)

messages in the messages pane..