How can a stored procedure return ROWCOUNT?

Question

Hi All I have written stored procedure which has 2 Insert queries and 1 Update query inside it. Of all these,either insert queries or update query are executed at a time. Now my problem is to get ROWCOUNT in each case. Say suppose if insert operations are executed,then I want stored procedure to return @@ROWCOUNT to the calling application, so that the application will be aware of whether the required operations executed correctly or not. Can anyone suggest/tell me how can I get the rows affected from the stored procedure?

Thanks & Regards

Padma

Answer 1

Use Output parameters in your stored procedures to return the RowCount of your inserts / updates.

Refer MSDN link for more information on how to use Output params

You can have multiple output params so you can have 2 different output params one each for your insert and the 3rd for your update statement.

Example:

CREATE PROCEDURE GetEmployeeData
   @employeeID INT,
   @managerID INT **OUTPUT**
AS
BEGIN
....
....

Additionally, you can always concatenate the rowcounts of your 2 Inserts / Update using delimiters and return them as one value eg: "10;0" - However that is the old fashioned and "I would not recommend" approach.

Also, you could create a table variable and return the table with rows = number of Inserts / updates and the value of the column = RowCount affected.