SQL Server stored procedure - Insert and Update DATETIME

I'm working with datetime in a stored procedure where I try to insert 1 record and update a second one accordingly.

How can I insert a new row with effectivedate = 2017-04-08 00:00:00.000 and close the previous effectivedateend to a date before that new effectivedate? Any guidance please?

Table CodeMarket looks like this:

+----+-------------------------+--------------------------+
|    |     effectivedate       |  effectivedateend        | 
+----+-------------------------+--------------------------+
| 1  | 2016-04-08 00:00:00.000 | NULL                     |
| 2  | 2015-04-08 00:00:00.000 | 2016-04-07 00:00:00.000  |
| 3  | 2014-04-08 00:00:00.000 | 2015-04-07 00:00:00.000  |
+----+-------------------------+--------------------------+

My stored procedure:

CREATE PROCEDURE [dbo].[SP_insertMarketing]
    (@effectivedate DATETIME,
     @result VARCHAR(10)
    )
AS 
BEGIN
    DECLARE @preveffectivedate DATETIME
    DECLARE @effectivedateend DATETIME

    BEGIN TRAN T1
    BEGIN
        SELECT @preveffectivedate = effectivedate
        FROM CodeMarket
        WHERE effectivedate = @effectivedate
          AND effectivedateend IS NULL

        IF NOT (@preveffectivedate IS NULL)
        BEGIN
            UPDATE CodeMarket
            SET effectivedateend = DATEADD(day, -1, @effectivedate)
            WHERE effectivedate = @preveffectivedate
        END

        --Add new record
        INSERT INTO CodeMarket 
        VALUES (CONVERT(DATETIME, '2017-04-08 00:00:00.000'), NULL)
   END
   END 
   COMMIT TRAN T1
   RETURN

1 answer

  • answered 2018-01-14 16:49 Janggi

    Why do you need @result? And I think declare one variable is enough for this task. Also I see that the date is hard-coded, but I think this should be based on the input variable from the procedure? Not sure if below is what you are looking for.

    Creating example table:

    CREATE TABLE CODEMARKET
    (
    effectivedate datetime,
    effectivedateend datetime)
    ;
    
    INSERT INTO CODEMARKET
    VALUES 
    ('2016-04-08 00:00:00.000',  NULL),
    ('2015-04-08 00:00:00.000', '2016-04-07 00:00:00.000'),
    ('2014-04-08 00:00:00.000', '2015-04-07 00:00:00.000') 
    ;
    GO
    

    Below is the procedure that takes the newdate:

        CREATE PROCEDURE [dbo].[SP_insertMarketing]
            (@newdate DATETIME)
        AS 
        BEGIN
            DECLARE @preveffectivedate DATETIME
    
            BEGIN TRAN T1
                BEGIN
                    SELECT @preveffectivedate = effectivedate
                    FROM CodeMarket
                    WHERE  effectivedateend IS NULL
    
                    IF NOT (@preveffectivedate IS NULL)
                    BEGIN
                        UPDATE CodeMarket
                        SET effectivedateend = @newdate
                        WHERE effectivedate = @preveffectivedate
                    END
    
                    --Add new record
                    INSERT INTO CodeMarket 
                    VALUES (CONVERT(DATETIME, @newdate), NULL)
               END
           COMMIT TRAN T1
           RETURN
        END
    

    Execution of this procedure can be done such as this:

    EXEC [dbo].[SP_insertMarketing] '2017-04-08 00:00:00.000'