Блог

У меня есть таблица, которая отслеживает вставки, обновления и удаления в другую таблицу. Этот шаблон использовался годами, но внезапно привел к ошибке, когда мы добавили отслеживание DATE столбца.

Это не проблема с форматом строки. Мои входные данные находятся в стандартном формате ISO-8601.

Я вырезал все, кроме необходимых частей кода, чтобы продемонстрировать проблему.

 CREATE TABLE dbo.ChangeTrackingTable
(
    oldValue VARCHAR(100) NULL,
    newValue VARCHAR(100) NULL
);
GO
CREATE PROCEDURE dbo.usp_TestProcedure
(
    @name VARCHAR(100),
    @dateOfBirth DATE
)
AS
BEGIN
    INSERT INTO dbo.ChangeTrackingTable
    (
        oldValue,
        newValue
    )
    SELECT
        List.oldFieldValue,
        List.newFieldValue
    FROM
        (VALUES
            (NULL, @name, IIF(@name IS NOT NULL, 1, 0)),
            (NULL, @dateOfBirth, IIF(@dateOfBirth IS NOT NULL, 1, 0))
        ) AS List (oldFieldValue, newFieldValue, hasChanges)
    WHERE
        List.hasChanges = 1
END;
GO
  

VALUES Список используется для динамического определения того, к каким столбцам прикасаются.

Когда я выполняю sproc только с датой, все работает нормально.

 DECLARE @date DATE = GETDATE();

EXEC dbo.usp_TestProcedure
    @name = NULL,
    @dateOfBirth = @date;

/*
oldValue    newValue
NULL    2019-03-27
*/

  

Но если я попытаюсь выполнить это с любым значением, указанным для @name , независимо от того, указано значение для @date или нет, я получаю следующую ошибку.

 DECLARE @date DATE = GETDATE();

EXEC dbo.usp_TestProcedure
    @name = 'Name',
    @dateOfBirth = @date;

--Conversion failed when converting date and/or time from character string.
  

Если я предоставляю жестко запрограммированные значения непосредственно в INSERT инструкцию, это работает нормально. Таким образом, я могу знать, что это не происходит на уровне таблицы при вставке.

Если я добавлю явное приведение к этой строке

 (NULL, CAST(@dateOfBirth AS VARCHAR(100)), IIF(@dateOfBirth IS NOT NULL, 1, 0))
  

это также работает.

Проблема возникает также с DATETIMEOFFSET , DATETIME и DATETIME2 типами, поэтому мое использование DATE не является проблемой.

Мой вопрос в том, почему я получаю string > datetime ошибку преобразования, когда пытаюсь прочитать DATE значение, которое нужно вставить в VARCHAR столбец, но только в VALUES списке, когда в результирующем наборе для List существует другое значение, не относящееся к дате?

Уточняя сам вопрос до его базовой структуры, я обнаружил основной ответ.

При использовании шаблона

 SELECT *
FROM
    (VALUES
        (NULL, @name, IIF(@name IS NOT NULL, 1, 0)),
        (NULL, @dateOfBirth, IIF(@dateOfBirth IS NOT NULL, 1, 0))
    ) AS List (oldFieldValue, newFieldValue, hasChanges)
  

SQL Server потребуется создать таблицу в памяти, и каждому из этих столбцов должен быть присвоен тип данных. Этот тип данных присваивается не произвольно, а методично в соответствии с хорошо документированным порядком приоритета типа данных.

Все типы даты отображаются очень высоко в этом списке, поэтому мое использование DATE имеет приоритет над любым типом CHAR или VARCHAR , который также отображается в таблице.

Столбцу в моей List таблице присвоен тип с наивысшим приоритетом: DATE .

Ошибка возникает, когда VARCHAR значение 'Name' неявно преобразуется в тип данных столбца, который является DATE . Не тогда, когда @date параметр вставлен в VARCHAR столбец таблицы.