title: SQL Server - How To Properly Update Table With User Defined Function In Update Statement?
body: <p>Have an issue attempting to update a table using a user defined function in the update statement.</p>
  
  <p>I have greatly simplified my existing SQL, and provided some sample code to show the issue that I am seeing.</p>
  
  <p>I start off with 300 rows in a test table all with the same timestamp value</p>
  
  <p>I need to group the 300 rows in MyTestTable into 3 sets of 100 rows with the same timestamp </p>
  
  <p>What I want to see is something like this:</p>
  
  <pre><code>Timestamp               Count   
  2016-04-01 15:51:00     100  
  2016-04-01 15:52:00     100  
  2016-04-01 15:53:00     100  
  </code></pre>
  
  <p>What I'm seeing right now is all 300 rows updated with the <strong>same</strong> timestamp:</p>
  
  <pre><code>Timestamp               Count  
  2016-04-01 15:51:00     300
  </code></pre>
  
  <p>What is the best way to formulate this query??</p>
  
  <p>Below is some simplified sample code that reproduces the problem    </p>
  
  <pre><code>CREATE TABLE [MyTestTable]
  (
      [ID] [int],
      [Timestamp] [smalldatetime]
  ) ON [PRIMARY]
  GO
  
  CREATE FUNCTION [dbo].[fn_MyTestFunction]
       (@StartTime smalldatetime,
        @EndTime smalldatetime,
        @RandomNumberOfSeconds int)
  RETURNS smalldatetime
  AS
  BEGIN
      DECLARE @Timestamp SMALLDATETIME
  
      -- Find an existing Timestamp between @StartTime and @EndTime in the MyTestTable
      -- with less than 100 rows with that timestamp
      SET @Timestamp = (SELECT TOP 1 [Timestamp] 
                        FROM MyTestTable
                        WHERE [Timestamp] BETWEEN @StartTime AND @EndTime
                        GROUP BY [Timestamp]
                        HAVING COUNT(*) &lt; 100)
  
      -- If no row found with timestamp between @StartTime and @EndTime
      -- or no timestamp found which has less than 100 rows with that timestamp
      -- Create a timestamp with a time somewhere between @StartTime and @EndTime
      if (@Timestamp is null)
      begin
          set @Timestamp = dateadd(ss, @RandomNumberOfSeconds, @StartTime)
      end
  
      return @Timestamp
  END
  GO
  
  declare @Counter int
  set @Counter = 0
  
  -- Populate the test table with 300 rows, all initially with the same timestamp value
  while @Counter &lt; 300
  begin
      insert MyTestTable (ID, [Timestamp]) values (@Counter, 'April 1, 2016')
      set @Counter = @Counter + 1
  end
  
  declare @StartTime smalldatetime
  declare @EndTime smalldatetime
  declare @RandomNumberOfSeconds float
  set @RandomNumberOfSeconds = 60
  
  set @StartTime = current_timestamp
  set @EndTime = dateadd(minute, 30, @StartTime)
  
  update MyTestTable
     set [Timestamp] = dbo.fn_MyTestFunction(@StartTime, @EndTime, @RandomNumberOfSeconds)
  
  select [Timestamp], count(*) as "Count"
  from MyTestTable
  group by [Timestamp]
  </code></pre>