• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3551
  • Last Modified:

Cursor Declare - The number of variables declared in the INTO list must match that of selected columns

I am declaring a cursor based on input parameters (as below) but when I run the code I get the error

Server: Msg 16924, Level 16, State 1, Line 107
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I cannot see a problem with the number of fields. All variables are declared etc.

Can anyone help

Thanks
Paul

Declare Keys_cursor CURSOR READ_ONLY FOR Select  + @LastNameField + ' , ' +
      @GenderField + ', ' +  @PostCodeField + ', ' +  @FirstNameField + ', ' +  
      @TitleField + ', ' +  @Add1Field + ', ' +  @Add2Field + ', ' +  @Add3Field  +
      ' from ' + @TableName + ' ' + @WhereSQL
--      + ', ' +  
--      @Key1Field + ', ' +  @Key2Field + ', ' +  @Key3Field + ', ' +  @Key4Field + ', ' +  
--      @Key5Field + ', ' +  @Key6Field + ', ' +  @Key7Field +
                              
OPEN Keys_cursor

-- Initialize the COM component.
 EXEC @retVal = sp_OACreate 'AdditionKeys.buildKeys', @comHandle OUTPUT
 IF (@retVal <> 0)
 BEGIN
 print 'Not Access DLL'
 END


FETCH NEXT FROM Keys_cursor
INTO @LastName, @Gender, @PostCode, @FirstName, @Title, @Add1, @Add2, @Add3
0
paullineham
Asked:
paullineham
1 Solution
 
lauszCommented:
try removing the first '+'

Your statement ..  Select  + @LastNameField  ....
Try with this ..  Select   @LastNameField  ....
0
 
paullinehamAuthor Commented:
Unfortunately the error still occurs.

If I hard code the fields as below it works fine

Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2,add3 from test_credit_data

If I try and use variables the error occurs.

Do you know if I can use a populated variable to pass to the query????
0
 
arbertCommented:
Why are you using a cursor on this--this could be handled with a simple select clause....
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
paullinehamAuthor Commented:
I need to use a cursor because I am actually updating alternative values later in the statement, using the passed fields and an external dll.

If I was to use an update sql statement I would need to run the same procedure multiple times so I have no choice. I have included the full script below to make my point unless you can provide an alternative.
Thanks

Declare Keys_cursor CURSOR FOR Select  + @LastNameField + ', ' +
      @GenderField + ', ' +  @PostCodeField + ', ' +  @FirstNameField + ', ' +  
      @TitleField + ', ' +  @Add1Field + ', ' +  @Add2Field + ', ' +  @Add3Field + ' from ' + @TableName + ' ' + @WhereSQL


--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2,add3 from test_credit_data
      
                        
OPEN Keys_cursor

-- Initialize the COM component.
 EXEC @retVal = sp_OACreate 'AdditionKeys.buildKeys', @comHandle OUTPUT
 IF (@retVal <> 0)
 BEGIN
 print 'Not Access DLL'
 END

FETCH NEXT FROM Keys_cursor
INTO @LastName,@Gender, @PostCode, @FirstName, @Title, @Add1, @Add2, @Add3

WHILE @@FETCH_STATUS = 0
BEGIN
      
       EXEC @retVal = sp_OAMethod @comHandle, 'Clear'
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
         print 'clear'
       end
      
       EXEC @retVal = sp_OASetProperty @comHandle, 'LastName', @vNewLastName=@LastName
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
         print 'LastName'
        END
      
       EXEC @retVal = sp_OASetProperty @comHandle, 'Gender', @vNewGender=@Gender
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'Gender'
        END
      
       EXEC @retVal = sp_OASetProperty @comHandle, 'PostCode', @vNewPostCode=@PostCode
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'PostCode'
        END
      
       EXEC @retVal = sp_OASetProperty @comHandle, 'FirstName', @vNewFirstName=@FirstName
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'FirstName'
        END
      
       EXEC @retVal = sp_OASetProperty @comHandle, 'Title', @vNewTitle=@Title
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'Title'
        END
      
      set @SetIndex = 0
       EXEC @retVal = sp_OASetProperty @comHandle, 'Address', @vNewAddress=@Add1, @nIndex = @SetIndex
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'Add1'
        END
      
      set @SetIndex = 1
       EXEC @retVal = sp_OASetProperty @comHandle, 'Address', @vNewAddress=@Add2, @nIndex =@SetIndex
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'Add2'
        END
      
      set @SetIndex = 2
       EXEC @retVal = sp_OASetProperty @comHandle, 'Address',  @vNewAddress=@Add3, @nIndex =@SetIndex
       IF (@retVal <> 0)
        BEGIN
      -- Trap errors if any
       print 'Add3'
        END
      
       EXEC @retVal = sp_OAMethod @comHandle, 'BuildStandardKeys'
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
         print 'method'
       end
      
       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key1Output OUTPUT, @KeyIndex1
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END

       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key2Output OUTPUT, @KeyIndex2
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END

       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key3Output OUTPUT, @KeyIndex3
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END
      
       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key4Output OUTPUT, @KeyIndex4
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END
            
       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key5Output OUTPUT, @KeyIndex5
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END

       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key6Output OUTPUT, @KeyIndex6
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END

       EXEC @retVal = sp_OAGetProperty @comHandle, 'Keys', @Key7Output OUTPUT, @KeyIndex7
       IF (@retVal <> 0)
        BEGIN
       -- Trap errors if any
             print @retVal
            EXEC @hr = sp_OAGetErrorInfo @comHandle, @source OUT, @description OUT
            IF @hr = 0
            BEGIN
                 set @retstring = '  Source: ' + @source + '  Description: ' + @description
            print @source
            print @description
            END
        END

      set @SQL = 'update ' + @TableName  +
            'SET ' + @Key1Field + ' = ' + char(39) + @Key1Output + char(39) + ', ' +
            @Key2Field  + ' = '  + char(39) +  @Key2Output  + char(39) +  ', ' +
            @Key3Field  + ' = '  + char(39) +  @Key3Output  + char(39) +  ', ' +  
            @Key4Field + ' = '  + char(39) +  @Key4Output  + char(39) +  ', ' +
            @Key5Field  + ' = '  + char(39) +  @Key5Output  + char(39) +   ', ' +
            @Key6Field  + ' = '  + char(39) +  @Key6Output  + char(39) +   ', ' +
            @Key7Field + ' ='  + char(39) +  @Key7Output  + char(39)  +
            'FROM ' + @TableName +
            ' WHERE CURRENT OF Keys_cursor '
      exec(@SQL)

   FETCH NEXT FROM Keys_cursor
   INTO @LastName,@Gender,@PostCode,@FirstName,@Title,@Add1,@Add2,@Add3
END

-- Release the reference to the COM object  
  EXEC sp_OADestroy @comHandle

CLOSE Keys_cursor    
DEALLOCATE Keys_cursor
GO
0
 
arbertCommented:
Stiil don't see why you need a cursor you can use a simple WHILE statement to loop.  Do you have a primary key on the table above?
0
 
paullinehamAuthor Commented:
Arbert

Sorry how do you mean. I've used while loops for running multiple statements but not for moving through a dataset. Can you let me know how you would do this.

Thanks
Paul
0
 
arbertCommented:
Do you have a primary key on the table???
0
 
paullinehamAuthor Commented:
yes - its built from two fields rec_id and field_id
0
 
LowfatspreadCommented:
can't help wonder why the code has to be so generic....

what's the design reason that the table names and column names
need to be varied....

whats the overhead of selecting all possible
columns from the table

agreed then the Update statement may get a bit more complex

but that should be capable of being handled by case statements....
this may even be a case where a CURSOR is required!


e.g. Select * from YourTable Where @where

fetch col1,col2,....

ok then invoke your OA's for the relevant set of column names

then
Update Table
 Set Col1 = case when @col1chg='y' then @newValue1 else col1 end
     ,Col2 = case when @col1chg='y' then @newValue2 else col2 end
    ,...
  where current of cursor...




 





0
 
arbertCommented:
This is just a quick and dirty I did against one of my tables, but you get the gist with the while statement:


declare @lastPrimarykey char(4)
declare @primarykey char(4)
declare @lastname varchar(100)

select @PrimaryKey='xxxx'
select @lastprimarykey=''

while @primarykey !=''

begin
  select distinct @lastname=last_name,@primarykey=pcn from hwbs_t_employees where pcn> @lastprimarykey order by pcn asc


select @lastprimarykey=@primarykey

end
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now