Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-12-05
10
Medium Priority
?
3,537 Views
Last Modified: 2007-12-19
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
Comment
Question by:paullineham
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 9881986
try removing the first '+'

Your statement ..  Select  + @LastNameField  ....
Try with this ..  Select   @LastNameField  ....
0
 

Author Comment

by:paullineham
ID: 9882237
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
 
LVL 34

Expert Comment

by:arbert
ID: 9882407
Why are you using a cursor on this--this could be handled with a simple select clause....
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:paullineham
ID: 9882422
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
 
LVL 34

Expert Comment

by:arbert
ID: 9882869
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
 

Author Comment

by:paullineham
ID: 9883001
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
 
LVL 34

Expert Comment

by:arbert
ID: 9883334
Do you have a primary key on the table???
0
 

Author Comment

by:paullineham
ID: 9883370
yes - its built from two fields rec_id and field_id
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9884326
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
 
LVL 34

Accepted Solution

by:
arbert earned 150 total points
ID: 9886146
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question