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

Answer 1

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