Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Unanswered: Cursor output then used by stored procedure?

    Hi, I am trying to achieve something that I hope is quite simple, but am confused about the best way.

    Basically, I have a select statement which is returning a series of ProductIDs. When I find the ones that meet my criteria I want to use those product IDs in an existing stored procedure which will update a Notes table.

    I can run the following which updates the notes when only one ProductID exists:

    declare @ProductID as varchar(30)
    DECLARE NOTEcursor cursor
    for
    *****Select Statement******
    OPEN NOTEcursor
    FETCH NEXT FROM NOTEcursor
    into @ProductID
    exec sp_insertnote @ProductID, 1, 'Here is my note'
    close NOTEcursor
    DEALLOCATE NOTEcursor

    However, when the select statement returns multiple values, I get this error:

    "Cannot insert the value NULL into column 'PRODUCTID', table Notes'; column does not allow nulls. INSERT fails.
    The statement has been terminated."

    The way I see it I can a) write something which fetches each row and passes it in turn into the proceedure (tried additional fetches, but couldn't get it working!) or b) maybe output the ProductIDs to a temp table first, but i'd still have the problem of how to pass them in turn to sp_insertnote.

    Please help!

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would see if your super top secret select statement reutrns any nulls for productid. Anyways where is your while loop to iterate through the whole cursor? And one more thing, do not prefix procedures with sp_.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    hi and thanks,

    not too top secret - just longwinded and would take up a lot of space! i am testing it where i know there are 2 results returned, so no issue with nulls.

    the while loop makes sense as a concept, but the syntax is a mystery to me. i can see plenty of examples everywhere, but none that are directly translatable to what i'm doing or show at what point the begin, continue or stored procedure are placed

  4. #4
    Join Date
    Jan 2009
    Posts
    6
    ok, i am making progress (of sorts!) but when i run the same commands and I print the output productID when there are only two of them, i get a continual loop of the first number being returned. Can anyone advise where the break should go?? i have tried a few things but no joy:

    declare @ProductID as varchar(30)
    DECLARE NOTEcursor cursor
    for
    *****Select Statement******
    OPEN NOTEcursor
    FETCH NEXT FROM NOTEcursor into @ProductID
    while @@FETCH_STATUS =0
    begin
    print @ProductID
    end
    close NOTEcursor
    DEALLOCATE NOTEcursor

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    think ive found it. i needed a second fetch added, and now seems to work

    Thanks Thras' for pointing me in the right direction

Posting Permissions

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