Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: declaring cursors

    I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

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

    If I use the second (commented out) statement which hard codes the fields the whole script works fine.

    Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

    Thanks
    Paul

    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

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

  2. #2
    Join Date
    Nov 2003
    Location
    Colombia
    Posts
    6

    Re: declaring cursors

    I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

    EXECUTE Select + @LastNameField + ', ' + @GenderField + ', ' + @PostCodeField + ', ' + @FirstNameField + ', ' + @TitleField + ', ' + @Add1Field + ', ' + @Add2Field + ', ' + @Add3Field + ' from ' + @TableName + ' ' + @WhereSQL

    so you'll can know if your statement is correct.

    bye,
    Maritzita

    Originally posted by plineham
    I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

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

    If I use the second (commented out) statement which hard codes the fields the whole script works fine.

    Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

    Thanks
    Paul

    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

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

  3. #3
    Join Date
    Dec 2003
    Posts
    5

    Re: declaring cursors

    To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

    Thanks for your help

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great....cursors are bad enough, noe we have dynamicx sql cursors...

    what are you trying to do?

    I hope it's an admin function...

    (as opposed to an applcation function)

    I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

    so....

    why bother?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    5
    We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

    If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

    However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

    I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

    Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You dude..no problem....just trying to help you..

    What do you mean by building keys?

    And this..

    Building the keys within SQL would be even more complicated.
    Doesn't make sense to me...

    mostly because I don't understand what building keys means....

    In any event...I hope it's working for you...

    AND it's FRIDAY...yeeeeHaaaaaaa

    Now if I can find my lost shaker of salt, I'd be in business..
    Last edited by Brett Kaiser; 12-08-03 at 11:35.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    It was friday but it is now monday. Aaaaaaa not good at all.

    The key I mean is essentially a string containing key components of an address

    for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

    To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

    I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

    The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

    Thanks for your help anyway

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What are the rules to build a "key" and how do you update it?

    Sounds like a job for a user defined function.

    If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

    Can you post some VB code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    5
    Find attached the classes we use to build some of the keys we use for address manipulation.

    If you know a way to build all these keys into separate fields through SQL I will be very grateful
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •