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?