Monday, March 12, 2012

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?

No comments:

Post a Comment