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,442 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
Comment Utility
try removing the first '+'

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

Author Comment

by:paullineham
Comment Utility
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
Comment Utility
Why are you using a cursor on this--this could be handled with a simple select clause....
0
 

Author Comment

by:paullineham
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:paullineham
Comment Utility
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
Comment Utility
Do you have a primary key on the table???
0
 

Author Comment

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

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now