How to Get the Number of Records Affected by a Stored Procedure

How can I get the number of records affected by a stored procedure?

Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.

Mind that if you have multiple INSERT/UPDATE/DELETE, you'll need a variable to store the result from @@ROWCOUNT for each operation.

Counting the number of rows returned by stored procedure

Select @@rowcount:

SELECT @@ROWCOUNT;

After executing the stored procedure.

Getting number of rows affected from cursor in stored procedure

This would be MUCH EASIER, cleaner, and ultimately faster without using any cursors at all - like this:

ALTER PROCEDURE [dbo].[Myproc] 
AS
    INSERT INTO [dbo].[tableA] ([Name], [Age])
        SELECT Name, Age
        FROM tableName;

    DECLARE @RowsInsertedStmt1 INT;
    
    SELECT @RowsInsertedStmt1 = @@ROWCOUNT;
    
    INSERT INTO [dbo].[tableA] ([Name], [Age])
        SELECT Name, Age
        FROM tableName;

    DECLARE @RowsInsertedStmt2 INT;
    
    SELECT @RowsInsertedStmt2 = @@ROWCOUNT;

    -- returned the combined count to the caller of the stored procedure
    SELECT TotalCount = @RowsInsertedStmt1 + @RowsInsertedStmt2;

Using the @@ROWCOUNT, you can find out how many rows were inserted.

Get number of records returned by the stored procedure

Add a column for the rowcount to your table variable:

DECLARE @UsersTable TABLE(TotalRows int, UserId int, ...

You can modify your generated SQL like:

SET @Query = '
    SELECT  TotalRows, UserId, ...
    FROM    (
            SELECT  ROW_NUMBER() OVER ('+ @OrderQuery +') AS RowNum
            ,       COUNT(*) OVER () AS TotalRows
            ,       ...
            ) AS t1
    WHERE   RowNum BETWEEN ...

Now your procedure will return the total rowcount in the first column.

getting number of records updated or inserted in sql server stored procedure

@@ROWCOUNT will show the number of rows affected by the most recent statement - if you have any statements between the INSERT and the PRINT then it will give you the wrong number.

Can you show us a little more code so we can see the order of execution?

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

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
}

---

Related Topics