CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Master Yoda
Master Yoda

Reputation: 4422

Nullable parameters during an update

Im trying to update a table based on several update increments.

My document review table currently looks like this:

| ID | Review Date         | Provisional Review Date | Final Review Date   | Provisional Review Timeframe (hours) | Final Review Timeframe (hours) |
|----|---------------------|-------------------------|---------------------|--------------------------------------|--------------------------------|
| 1  | 2018-03-12 00:00:00 | 2018-03-10 00:00:00     | 2018-03-11 00:00:00 | 24                                   | 48                             |

The Stored Procedure (insertDocumentReview) looks like this:

CREATE PROCEDURE [dbo].[insertDocumentReview]
    --parameters
    @id int,
    @ReviewDate datetime2 = null,
    @ProvisionalReviewDate datetime2 = null,
    @FinalReviewDate datetime2 = null,
AS
BEGIN

        UPDATE  [DocumentReview] 
        SET     ReviewDate = @ReviewDate,
                ProvisionalReviewDate = @ProvisionalReviewDate,
                FinalReviewDate = @FinalReviewDate,
                --the following columns are calculated based on the values of the columns populated above
                [ProvisionalReviewTimeframe] = (DATEDIFF(SECOND, [ProvisionalReviewDate], [ReviewDate]) / 3600.0),
                [lReviewTimeframe] = (DATEDIFF(SECOND, [FinalReviewDate], [ReviewDate]) / 3600.0),
        WHERE   [Id] = @id

END

The problem

I then execute the stored procedure 4 times with varying parameters set to null like this:

-- 1) Insert Final Review Date
EXEC insertDocumentReview 1, NULL, NULL, '2018-03-14 10:19:10'

-- 2) Insert Provisional Review Date
EXEC insertDocumentReview 1, NULL, '2018-03-13 12:19:10', NULL

-- 3) Insert Review Date
EXEC insertDocumentReview 1, '2018-03-15 10:19', NULL, NULL

-- 4) Insert all dates
EXEC insertDocumentReview 1, '2018-03-15 10:19', '2018-03-13 12:19:10', '2018-03-14 10:19:10'

I need to know why the calculation does not calculate at execution 3.

Upvotes: 1

Views: 43

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

It is all or nothing behavior. You cannot refer to value from same execution and get new value.

Example:

create table t(x INT, y INT);
INSERT INTO t VALUES (5, 10);
update t
SET x = y,
    y = x;
select * from T;

DBFiddle Demo

-- Before
x = 5, y = 10
-- After
x = 10, y = 5
-- in step 3 you want this to work like this
x= 10, y = 10

Values are swapped, but in step 3 you want to get new value from prev column, which won't happen.

I suggest to try:

UPDATE  [DocumentReview] 
SET     ReviewDate = @ReviewDate,
        ProvisionalReviewDate = @ProvisionalReviewDate,
        FinalReviewDate = @FinalReviewDate,

      [ProvisionalReviewTimeframe] 
        = (DATEDIFF(SECOND, [ProvisionalReviewDate], @ReviewDate) / 3600.0),
      [lReviewTimeframe] 
        = (DATEDIFF(SECOND, [FinalReviewDate], @ReviewDate) / 3600.0),
WHERE   [Id] = @id

Upvotes: 2

Related Questions