Results 1 to 5 of 5

Thread: Refactor code with cursor - different results

Threaded View

  1. #1
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92

    Refactor code with cursor - different results

    I am refactoring a stored proc, the intention being to replace a cursor with more efficient code. the outline is as follows.

    DECLARE @Table abc
    INSERT INTO @Table ... SELECT From ...
    UPDATE @Table SET field1 = 'a', field2 = 'b'

    -- Code with cursor here: ----------------
    DECLARE CURSOR x FOR SELECT IDField, field3, field4 FROM @Table
    OPEN x
    WHILE ...
    SELECT @Var1 = fielda, @Var2 = fieldb from TableValuedfunctionx(@field3, @field4)

    UPDATE @Table SET field5 = @Var1, field6 = @Var2 where IDField = @IDField
    END
    ------------------------------------------
    -- Replacement code here: ----------------
    UPDATE @Table
    SET field5 = (SELECT fielda from TableValuedfunctionx(field3, field4),
    SET field6 = (SELECT fieldb from TableValuedfunctionx(field3, field4)
    ------------------------------------------



    When I SELECT * from @Table at the end of this code, some of the values in field5 and field6 are null where they had values when I was using a cursor.
    Note: the function TableValuedfunctionx is fairly complex, declaring multiple table variables and joining these to various other tables to produce the table it returns.
    Is the problem here the fact I'm calling this function multiple times in the one UPDATE statement or is it something else?
    Last edited by bobbo; 06-24-2009 at 05:31 AM.

Posting Permissions

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