CODEWITHSUNDEEP
sqlsql-servert-sql
Jack Allen
Jack Allen

Reputation: 121

SQL Server - How To Properly Update Table With User Defined Function In Update Statement?

Have an issue attempting to update a table using a user defined function in the update statement.

I have greatly simplified my existing SQL, and provided some sample code to show the issue that I am seeing.

I start off with 300 rows in a test table all with the same timestamp value

I need to group the 300 rows in MyTestTable into 3 sets of 100 rows with the same timestamp

What I want to see is something like this:

Timestamp               Count   
2016-04-01 15:51:00     100  
2016-04-01 15:52:00     100  
2016-04-01 15:53:00     100

What I'm seeing right now is all 300 rows updated with the same timestamp:

Timestamp               Count  
2016-04-01 15:51:00     300

What is the best way to formulate this query??

Below is some simplified sample code that reproduces the problem 

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(*) < 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 < 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]

Upvotes: 1

Views: 2499

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

An update statement completes as a single transaction. That means that the changes to the table are not visible until the transaction is committed.

Your code seems to be assuming that the update commits one row at a time -- that each invocation of the function sees a different version of the table. But, that is not how SQL works. While the update is executing, any reference to the table sees the "old" values. The "new" values are not apparent until the commit takes place.

Upvotes: 3

Related Questions