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
Solved

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

Posted on 2003-12-05
10
3,474 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 50 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 25
MS SQL: Create User Function to Remove Long Words 5 32
SQL R 21 26
MS SQL query to show nearest date 6 37
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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