2

I have a scenario where I am looping through a resultset within a transaction and I need to INSERT a unique datetime value within a table for each iteration through the resultset - will GetDate() be recalculated each time or will it only be calculated the first time and then be the same for each iteration through the loop?

My pseudo-code is below:

BEGIN TRANSACTION
GO

DECLARE @ID INT 
DECLARE @table TABLE (/* Columns */) 

WHILE (SELECT COUNT(*) FROM @table WHERE PROCESSED = 0) > 0
      BEGIN

            SELECT TOP 1 @ID = ID FROM @table WHERE PROCESSED = 0 

            -- INSERT GetDate() into child table at this point. 
            -- Will GetDate() be re-evaluated each time? 

            UPDATE @table SET PROCESSED = 1 WHERE ID = @ID 

      END

END TRANSACTION
GO

Thanks in advance!

Ben S
  • 68,394
  • 30
  • 171
  • 212
Robert W
  • 2,921
  • 7
  • 34
  • 42
  • 1
    Btw, you might want to use GetUtcDate() instead of GetDate() to avoid ambiguous Daylight Savings Time timestamps. – JasDev Dec 04 '09 at 15:38
  • Why on earth are you even doing a loop? Do a set-based insert instead. – HLGEM Dec 04 '09 at 16:50

1 Answers1

7

Yes.

If you want to avoid re-evaluating it, store its value in a variable before the loop, and insert that instead.

Ben S
  • 68,394
  • 30
  • 171
  • 212