CODEWITHSUNDEEP
t-sqldatetimenull
NealR
NealR

Reputation: 10679

INSERT null datetime value into SQL table

Below is a stored procedure we use are using to populate a table in an SQL database. The @expDate is a parameter that is sent in from a C# batch job, which gets it's values from and Excel spreadsheet.

If the training for the current agent is active, the spreadsheet will not have a value in the expiration date cell. This causes a blank string to be sent to the stored procedure from the C# code, which then gets translated into the datetime value 1900-01-01. This is fine up until the point where I need to insert new records into the table using the cursor.

If the @expDate value is 1900-01-01, then I need the value in the SQL record's expiration date field to be NULL. However, I cannot set @expDate = NULL nor can I simply set it to blank ('') [EDIT: I cannot set to blank due to a business rule. A blank value will cause the field's value to be set at 1900-01-01]. Any suggestions? 

    DECLARE @date datetime = GETDATE()
    IF @expDate = '1900-01-01' SET @flag = '1'
    IF @expDate = '1900-01-01' SET @expDate = GETDATE()

    DECLARE prod_cursor CURSOR LOCAL SCROLL FOR
    SELECT ProductCode 
    FROM CourseToProduct 
    WHERE CourseCode = @courseCode and (TerminationDate >= @expDate OR TerminationDate IS NULL)

    IF @flag = '1' SET @expDate = ''

    OPEN prod_cursor

    FETCH NEXT FROM prod_cursor
    INTO @productCode

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF NOT EXISTS
        (
        SELECT *
        FROM AgentProductTraining
        WHERE @sNumber = SNumber and 
              @courseCode = CourseCode and 
              @productCode = ProductCode and 
              @dateTaken = DateTaken
        )
        BEGIN
            IF @sNumber IS NOT NULL
            BEGIN
                INSERT INTO AgentProductTraining
                            (
                             SNumber,
                             CourseCode,
                             ProductCode,
                             DateTaken,
                             DateExpired,
                             LastChangeOperator,
                             LastChangeDate
                            ) 
                VALUES      (
                             @sNumber,
                             @courseCode,
                             @productCode,
                             @dateTaken,
                             COALESCE(@expDate, 'NULL'),
                             @lastChangeOperator,
                             @lastChangeDate
                           )    
            END
        END

        FETCH NEXT FROM prod_cursor
        INTO @productCode
    END
    CLOSE prod_cursor;
    DEALLOCATE prod_cursor;

Upvotes: 1

Views: 28366

Answers (2)

PaulStock
PaulStock

Reputation: 11263

Will this work?

                INSERT INTO AgentProductTraining
                        (
                         SNumber,
                         CourseCode,
                         ProductCode,
                         DateTaken,
                         DateExpired,
                         LastChangeOperator,
                         LastChangeDate
                        ) 
            VALUES      (
                         @sNumber,
                         @courseCode,
                         @productCode,
                         @dateTaken,
                         CASE WHEN @expDate = '1900-01-01' THEN Null ELSE @expDate END, 
                         @lastChangeOperator,
                         @lastChangeDate
                       )

Upvotes: 1

NealR
NealR

Reputation: 10679

By following Oded's comments, I got rid of the COALESCE statement and set the @flag value to NULL as opposed to NULL. By change this line of code: 

IF @flag = '1' SET @expDate = ''

to this: 

IF @flag = '1' SET @expDate = NULL

The sproc now works.

Upvotes: 2

Related Questions