Return rows affected from a Stored Procedure on each INSERT to display in ASP.NET page

Question

I have a stored procedure that contains like 10 different INSERTS, is it possible to return the COUNT of the rows affected on each INSERT to ASP.NET c# page so i can display Stored Procedure process for the client viewing that ASP.NET page?

Accepted Answer

On the server side send the message to the client using `RAISERROR` function with severity 10 (severity higher than 10 causes exception that breaks procedure execution, i.e. transfers execution to the `CATCH` block, if there is one). In the following example I haven't added error number, so the default error number of 50000 will be used by `RAISERROR` function. Here is the example:

```
DECLARE @count INT = 0
DECLARE @infoMessage VARCHAR(1000) = ''

-- INSERT

SET @count = @@ROWCOUNT
SET @infoMessage = 'Number of rows affected ' + CAST(@count AS VARCHAR(10))
RAISERROR(@infoMessage, 10, 0) WITH NOWAIT

-- another INSERT

SET @count = @@ROWCOUNT
SET @infoMessage = 'Number of rows affected ' + CAST(@count AS VARCHAR(10))
RAISERROR(@infoMessage, 10, 0) WITH NOWAIT
```

On the client side, set the appropriate event handlers, here is an example:

```
using (SqlConnection conn = new SqlConnection(...))
{
    conn.FireInfoMessageEventOnUserErrors = true;
    conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
    using (SqlCommand comm = new SqlCommand("dbo.sp1", conn) 
           { CommandType = CommandType.StoredProcedure })
    {
        conn.Open(); 
        comm.ExecuteNonQuery();
    }
}

static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    // Process received message
}
```

Return rows affected from a Stored Procedure on each INSERT to display in ASP.NET page

Solution

Problem

Related problems