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,482 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

726 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