1

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;
6
  • "I cannot set @expDate = NULL nor can I simply set it to blank ('')". Why not? Commented Nov 6, 2012 at 20:19
  • It's a business rule, a blank value will set the column to 1900-01-01 (just made the edit) Commented Nov 6, 2012 at 20:24
  • A business rule? For an implementation detail??? If you need to set the value of the field to null (which you state you need to), what's the issue with setting the variable to null? Where does the business come into it? Commented Nov 6, 2012 at 20:26
  • It's a datetime field. You can't set a variable value to be 'NULL' or anything other than datetime or it won't work. You will get this error message: "Conversion failed when converting date and/or time from character string." Commented Nov 6, 2012 at 20:31
  • 2
    How are you trying to set it? If you are using 'NULL', that's wrong - that's the string NULL. You need to pass in the actual NULL (what you do in your COALESCE is wrong). Commented Nov 6, 2012 at 20:35

2 Answers 2

2

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.

Sign up to request clarification or add additional context in comments.

Comments

1

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
                       )    

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.