Cursorfetch: The number of variables declared in the INTO list must match that of selected columns in SQL Server 2012

Question

I created a stored procedure that validates certain columns given a table as parameter and decided to use Cursor. But I am getting this cursor fetch error when trying to execute the stored procedure.

I already double checked the columns and it matches the number of variables in INTO list. I tried modifying the stored procedure by writing this

SELECT Lot, ItemId, PO, Status, ErrorDetails 
FROM Table1

instead of

SELECT @SQLSTATEMENT

after

SET @MyCursor = CURSOR FOR

and it works fine. But I want the source table to be a parameter so this won't work for me. Any ideas?

CREATE PROCEDURE [dbo].[ValidateData]
    @TABLENAME_PARAM NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MyCursor CURSOR;
    DECLARE @CustomerLot  NVARCHAR(100),
            @DeviceName   NVARCHAR(100),
            @PO           NVARCHAR(100),
            @Status       NVARCHAR(1),
            @ErrorDetails NVARCHAR(250);
    DECLARE @TABLENAME  NVARCHAR(100);
    DECLARE @SQLSTATEMENT AS NVARCHAR(MAX);

    SELECT @TABLENAME = Quotename (TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @TABLENAME_PARAM

    SET @SQLSTATEMENT = 'Select Lot, ItemId, PO, Status, ErrorDetails FROM ' + @TABLENAME + ' WHERE  Status = ''N'''

    BEGIN
        SET @MyCursor = CURSOR FOR 
            SELECT @SQLSTATEMENT

        OPEN @MyCursor

        FETCH NEXT FROM @MyCursor INTO @CustomerLot, @DeviceName, @PO, @Status, @ErrorDetails

        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRAN
                --some validations here
                COMMIT TRAN

                FETCH NEXT FROM @MyCursor INTO @CustomerLot, @DeviceName, @PO, @Status, @ErrorDetails
        END;

        CLOSE @MyCursor;
        DEALLOCATE @MyCursor;
    END
END
GO

Possible duplicate of [Using a cursor with dynamic SQL in a stored procedure](https://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure) — Dale K, May 20 '19 at 03:34

score 0 · Accepted Answer · answered May 20 '19 at 04:25

Try this-

CREATE PROCEDURE [dbo].[ValidateData] @TABLENAME_PARAM NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    --DECLARE @MyCursor CURSOR;
    DECLARE 
            @CustomerLot  NVARCHAR(100),
            @DeviceName   NVARCHAR(100),
            @PO           NVARCHAR(100),
            @Status       NVARCHAR(1),
            @ErrorDetails NVARCHAR(250);
    DECLARE @TABLENAME  NVARCHAR(100);
    DECLARE @SQLSTATEMENT AS NVARCHAR(MAX);

    SELECT @TABLENAME = Quotename (TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @TABLENAME_PARAM

    SET @SQLSTATEMENT = 'DECLARE  MyCursor CURSOR FOR Select Lot, ItemId, PO, Status, ErrorDetails FROM ' + @TABLENAME + ' WHERE  Status = ''N'''
    EXEC sp_executesql @sqlstatement
    OPEN MyCursor

    FETCH NEXT FROM MyCursor INTO @CustomerLot, @DeviceName, @PO, @Status, @ErrorDetails

    WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRAN
            --some validations here
            COMMIT TRAN

            FETCH NEXT FROM MyCursor INTO @CustomerLot, @DeviceName, @PO, @Status, @ErrorDetails
        END;

        CLOSE MyCursor;
        DEALLOCATE MyCursor
    END
END
GO

Its desirable to flag as a duplicate rather than answering when the answer already exists on the site. — Dale K, May 20 '19 at 05:00

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns in SQL Server 2012

1 Answers1