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?


            -- 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
            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
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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?

Jim HornSQL Server Data DudeCommented:
>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Better yet, stop writing multiple FETCHes for every cursor, which is an invitation to these type of errors.

            FROM      TPROJECT
            WHERE      PROJECT_ID > 0 AND PROJ_STAT_AVT_ID IN (124, 123, 126, 125, 118)

            OPEN Project_Cursor;

            WHILE 1 = 1
                FETCH NEXT FROM Project_Cursor INTO @ProjectID, @ProjTypeID, @ClientID;
                IF @@FETCH_STATUS <> 0
                    IF @@FETCH_STATUS = -1
                /*process cursor rows here*/
            END /*WHILE*/
            DEALLOCATE Project_Cursor
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Chuck LoweAuthor 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 DBACommented:
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.
Chuck LoweAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.