We help IT Professionals succeed at work.

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

IN SLQServer 2012 - SQL Server Management Studio

Modifying a stored procedure that has been running forever using one variable for the cursor. I'm trying to use
at least 5 variables for the cursor but get this error when I go over 2 variables.
Using cursors of 1 or 2 variables is no problem no matter what name or what order they are in.
But When trying more than 2 variables I get the error.

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

I have tried interchanging all different names and combinations that work together as 2 variables so
I know it's not due to type mismatch. But when I add one that works in combination as 2 variables in the code
as a 3rd variable I get the error. Same goes for 4 or 5 or 6 etc variables.

PROJECT_ID,PROJ_TYPE_ID,CLIENT_ID of TPROJECT are all of type integer in the table

What am I missing?

      BEGIN TRY

            -- Start with return flag set to Success.
            SET @SuccessFlag = 0

            DECLARE @ProjectID      INT = NULL
            DECLARE @ProjTypeID     INT = NULL
            DECLARE @ClientID       INT = NULL
            DECLARE @RecoveryID       INT = NULL
            DECLARE @ProjectName      VARCHAR(255) = NULL

            DECLARE Project_Cursor CURSOR FOR
            SELECT      PROJECT_ID,PROJ_TYPE_ID,CLIENT_ID
            FROM      TPROJECT
            WHERE      PROJECT_ID > 0 AND PROJ_STAT_AVT_ID IN (124, 123, 126, 125, 118)

            -- Open the recordset
            OPEN Project_Cursor;
            -- Start with the first Id
            FETCH NEXT FROM Project_Cursor INTO @ProjectID, @ProjTypeID, @ClientID;

            -- Loop through the recordset
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
Commented:
Do you know that the failure is on this FETCH statement an not the one at the bottom of the loop?  And are your exit condition and handlers properly defined and implemented?

Kent
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>FETCH NEXT FROM Project_Cursor INTO @ProjectID, @ProjTypeID, @ClientID;
Ok, there are three variables here.  Show us the rest of your cursor, specifically the FETCH NEXT FROM line at the bottom which should also have three variables.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Better yet, stop writing multiple FETCHes for every cursor, which is an invitation to these type of errors.

            DECLARE Project_Cursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT      PROJECT_ID,PROJ_TYPE_ID,CLIENT_ID
            FROM      TPROJECT
            WHERE      PROJECT_ID > 0 AND PROJ_STAT_AVT_ID IN (124, 123, 126, 125, 118)

            OPEN Project_Cursor;

            WHILE 1 = 1
            BEGIN
                FETCH NEXT FROM Project_Cursor INTO @ProjectID, @ProjTypeID, @ClientID;
                IF @@FETCH_STATUS <> 0
                    IF @@FETCH_STATUS = -1
                        BREAK
                    ELSE
                        CONTINUE;
                /*process cursor rows here*/
            END /*WHILE*/
 
            DEALLOCATE Project_Cursor

Author

Commented:
I split the point between the 2 of you. This is my first ever stored procedure using a cursor. It was code already in place (no comments, no author etc). So when I first added a second variable I did put it at the bottom fetch. But never working with cursors before, as I went on to make more modifications to it I neglected to update the bottom fetch with the additional variables.

Thanks for the catch.

I'm not sure why the coder decided to use cursors. Of all the stored procs in the system (front end being access, backend being SQL Server) this is the only one using cursors. It creates a temp table first and then goes on to insert values from another table and later on update other values from other tables. Then generates an excel spreadsheet.

If you have time, what advantage do you think using a cursor has over simply creating a temp table, inserting the values from the master table and updating the other values later?

Thanks again.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
There's no advantage to a cursor if the same thing can be done in a set-based way.  Cursors are always a last resort, used when there's no other good way to do it.

Author

Commented:
@Scott
That's what I thought. It runs very, very slow. In fact it is the slowest of all the procedures in the system and doesn't go after a lot of data or do a lot of data manipulation. I believe I will rewrite it when I have some down time. (ha ha).

Thanks again for the help. I was going crazy. I remember way back when, the SQL instructor saying "I'll hit on cursors in the class but I suggest and hope you never use them!" Now I know why. As you say as a last resort. Looking at the code it can be rewritten so it runs faster but alas I'm under a deadline just to get it changed.