Getting number of rows affected from cursor in stored procedure-CodePudding

Below is my stored procedure which has 2 cursors inserting data into the same table. There is different logic in both cursors, which I have removed for this question. How to get the number of total rows affected or inserted during the whole operation?

ALTER PROCEDURE [dbo].[Myproc] 
AS
    DECLARE @fetch_status AS INT

    DECLARE mycur CURSOR read_only FOR
        SELECT * FROM tableName

    OPEN mycur

    FETCH next FROM mycur INTO @Name, @Age
    SELECT @fetch_status = @@FETCH_STATUS

    WHILE @fetch_status = 0
    BEGIN
        SET nocount ON;

        INSERT INTO [dbo].[tableA] ([Name], [Age])
        VALUES (@Name, @Age)

        FETCH next FROM mycur INTO @Name, @Age
        SELECT @fetch_status = @@FETCH_STATUS
    END

    CLOSE mycur
    DEALLOCATE mycur


    DECLARE mycur CURSOR read_only FOR
        SELECT * FROM tablename

    OPEN mycur

    FETCH next FROM mycur INTO @Name, @Age
    SELECT @fetch_status = @@FETCH_STATUS

    RAISERROR ('Starting second', 10, 0) WITH nowait

    DECLARE @msg AS NVARCHAR(50)

    WHILE @fetch_status = 0
    BEGIN
        SET nocount ON;

        INSERT INTO [dbo].[tableA] ([Name], [Age])
        VALUES (@Name, @Age)

        FETCH next FROM mycur INTO @Name, @Age
        SELECT @fetch_status = @@FETCH_STATUS
     END

     CLOSE mycur
     DEALLOCATE mycur

CodePudding user response：

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;

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