asked on
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,CL
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,CL
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
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,CL
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,CL
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
Microsoft SQL ServerGolangSQL
Last Comment
Chuck Lowe
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
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.
ASKER
@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.
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.
DECLARE Project_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT PROJECT_ID,PROJ_TYPE_ID,CL
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