Вопрос или проблема
Я создал хранимую процедуру.
USE [MYDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BulkUpsertClientMatterInfo]
@ClientMatterInfo dbo.ClientMatterInfoType READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RejectedRows TABLE (
Client_Number NVARCHAR(50),
Matter_Number NVARCHAR(50),
ErrorMessage NVARCHAR(4000)
);
DECLARE @Client_ID INT;
DECLARE @Client_Number NVARCHAR(50);
DECLARE @Client_Name NVARCHAR(500);
DECLARE @Matter_ID INT;
DECLARE @Matter_Number NVARCHAR(50);
DECLARE @Matter_Name NVARCHAR(500);
DECLARE @AttyID INT;
DECLARE @PrimaryTpkr NVARCHAR(500);
DECLARE @OrigTpkr NVARCHAR(500);
DECLARE @SecondaryTpkr NVARCHAR(500);
DECLARE @MatterStatus NVARCHAR(50);
DECLARE @AreaOfLaw NVARCHAR(100);
DECLARE @Date_Created DATETIME;
DECLARE @Closed_Date DATETIME;
DECLARE @LastBilledDate DATETIME;
DECLARE @LastBilledBy NVARCHAR(100);
DECLARE @EthicalWall NVARCHAR(10);
DECLARE @LegalHold NVARCHAR(10);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT
Client_ID, Client_Number, Client_Name, Matter_ID, Matter_Number, Matter_Name,
AttyID, PrimaryTpkr, OrigTpkr, SecondaryTpkr, MatterStatus, AreaOfLaw,
Date_Created, Closed_Date, LastBilledDate, LastBilledBy, EthicalWall, LegalHold
FROM @ClientMatterInfo;
OPEN cur;
FETCH NEXT FROM cur INTO
@Client_ID, @Client_Number, @Client_Name, @Matter_ID, @Matter_Number, @Matter_Name,
@AttyID, @PrimaryTpkr, @OrigTpkr, @SecondaryTpkr, @MatterStatus, @AreaOfLaw,
@Date_Created, @Closed_Date, @LastBilledDate, @LastBilledBy, @EthicalWall, @LegalHold;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
MERGE INTO dbo.ClientMatterInfo_New AS target
USING (SELECT @Client_ID AS Client_ID, @Client_Number AS Client_Number, @Client_Name AS Client_Name,
@Matter_ID AS Matter_ID, @Matter_Number AS Matter_Number, @Matter_Name AS Matter_Name,
@AttyID AS AttyID, @PrimaryTpkr AS PrimaryTpkr, @OrigTpkr AS OrigTpkr,
@SecondaryTpkr AS SecondaryTpkr, @MatterStatus AS MatterStatus,
@AreaOfLaw AS AreaOfLaw, @Date_Created AS Date_Created,
@Closed_Date AS Closed_Date, @LastBilledDate AS LastBilledDate,
@LastBilledBy AS LastBilledBy, @EthicalWall AS EthicalWall,
@LegalHold AS LegalHold) AS source
ON target.Client_Number = source.Client_Number AND target.Matter_Number = source.Matter_Number
WHEN MATCHED THEN
UPDATE SET
target.Client_ID = COALESCE(source.Client_ID, target.Client_ID),
target.Client_Name = COALESCE(source.Client_Name, target.Client_Name),
target.Matter_ID = COALESCE(source.Matter_ID, target.Matter_ID),
target.Matter_Name = COALESCE(source.Matter_Name, target.Matter_Name),
target.AttyID = COALESCE(source.AttyID, target.AttyID),
target.Primary_tpkr = COALESCE(source.PrimaryTpkr, target.Primary_tpkr),
target.Orig_tpkr = COALESCE(source.OrigTpkr, target.Orig_tpkr),
target.Secondary_tpkr = COALESCE(source.SecondaryTpkr, target.Secondary_tpkr),
target.MatterStatus = COALESCE(source.MatterStatus, target.MatterStatus),
target.AreaOfLaw = COALESCE(source.AreaOfLaw, target.AreaOfLaw),
target.Date_created = COALESCE(source.Date_Created, target.Date_created),
target.Closed_date = COALESCE(source.Closed_Date, target.Closed_date),
target.LastBilledDate = COALESCE(source.LastBilledDate, target.LastBilledDate),
target.LastBilledBy = COALESCE(source.LastBilledBy, target.LastBilledBy),
target.EthicalWall = COALESCE(source.EthicalWall, target.EthicalWall),
target.LegalHold = COALESCE(source.LegalHold, target.LegalHold),
target.LastActivityDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Client_ID, Client_Number, Client_Name, Matter_ID, Matter_Number, Matter_Name, AttyID,
Primary_tpkr, Orig_tpkr, Secondary_tpkr, MatterStatus, AreaOfLaw, Date_created,
Closed_date, LastBilledDate, LastBilledBy, EthicalWall, LegalHold, LastActivityDate)
VALUES (source.Client_ID, source.Client_Number, source.Client_Name, source.Matter_ID,
source.Matter_Number, source.Matter_Name, source.AttyID, source.PrimaryTpkr,
source.OrigTpkr, source.SecondaryTpkr, source.MatterStatus, source.AreaOfLaw,
source.Date_Created, source.Closed_Date, source.LastBilledDate,
source.LastBilledBy, source.EthicalWall, source.LegalHold, GETDATE());
END TRY
BEGIN CATCH
INSERT INTO @RejectedRows (Client_Number, Matter_Number, ErrorMessage)
VALUES (@Client_Number, @Matter_Number, ERROR_MESSAGE());
END CATCH;
FETCH NEXT FROM cur INTO
@Client_ID, @Client_Number, @Client_Name, @Matter_ID, @Matter_Number, @Matter_Name,
@AttyID, @PrimaryTpkr, @OrigTpkr, @SecondaryTpkr, @MatterStatus, @AreaOfLaw,
@Date_Created, @Closed_Date, @LastBilledDate, @LastBilledBy, @EthicalWall, @LegalHold;
END
CLOSE cur;
DEALLOCATE cur;
-- Возврат отклонённых строк с сообщениями об ошибках
SELECT * FROM @RejectedRows;
END;
Я создал этот SQL, который принимает TVP, и намерен отправить несколько строк за один раз. Поэтому я использую курсор в этом случае. Однако, если в какой-либо из строк произошла ошибка, вы можете видеть, что я возвращаю таблицу RejectedUsers.
Но в случае неверных данных datetime, когда мой курсор сопоставляет данные из TVP, он выдает ошибку прямо там. Идеальный случай, который я хочу, это чтобы все мои ошибки отображались через таблицу RejectedUsers.
Смотрите, я пытаюсь выполнить эту хранимую процедуру с неправильной датой, чтобы получить ошибку:
-- Объявить переменную типа таблицы
DECLARE @TestClientMatterInfo dbo.ClientMatterInfoType;
-- Вставить образцы строк только с допустимыми данными
INSERT INTO @TestClientMatterInfo (Client_ID, Client_Number, Client_Name, Matter_ID,
Matter_Number, Matter_Name,
AttyID, PrimaryTpkr, OrigTpkr, SecondaryTpkr,
MatterStatus, AreaOfLaw,
Date_Created, Closed_Date, LastBilledDate, LastBilledBy,
EthicalWall, LegalHold)
VALUES
(1, 'C1056', 'Client sixty', 101, 'M1051', 'Matter sixty', 1, 'Primary1updated',
'Orig1', 'Secondary1','Open', 'Litigation', '2024-01-01G!', '2024-12-01', '2024-01-
01', 'User1', 'Yes', 'No'); -- Допустимая строка
-- Выполнить хранимую процедуру только с допустимыми тестовыми данными
EXEC BulkUpsertClientMatterInfo @TestClientMatterInfo
Но я получаю ошибку от самой хранимой процедуры, вместо таблицы RejectedUsers.
Msg 241, Level 16, State 1, Line 82 Конвертация не удалась при конвертации
даты и/или времени из символьной строки.Время завершения: 2024-11-13T13:12:23.4853946+05:00
Ответ или решение
Проблема преобразования дат в хранимой процедуре с использованием курсора в SQL
При работе с хранимыми процедурами и курсорами в SQL Server вы можете столкнуться с ошибками, такими как "Conversion failed when converting date and/or time from character string". Это сообщение об ошибке возникает, когда происходит попытка преобразования строки в формат даты и времени, но строка имеет недопустимый формат. Рассмотрим более подробно, как решить эту проблему.
Описание проблемы
В вашей хранимой процедуре BulkUpsertClientMatterInfo используется таблица переменного типа @ClientMatterInfo для приема нескольких строк данных. Однако, если одна из строк содержит недопустимые даты (например, ‘2024-01-01G!’), это вызывает ошибку преобразования, что останавливает выполнение процедуры и не позволяет собрать все ошибки в результирующую таблицу @RejectedRows.
Решение проблемы
Для того чтобы получить все ошибки в одной таблице, необходимо изменить логику обработки данных. Важно убедиться, что до того, как значения будут переданы в переменные даты, вы проверяете их на корректность.
Вот несколько предложений для исправления кода:
-
Изменение структуры курсора: Вместо непосредственной передачи значений в переменные даты, используйте временные переменные, которые будут проверены на корректность.
-
Проверка формата дат: Используйте функцию
TRY_CASTилиTRY_CONVERT, чтобы избежать ошибок преобразования. Эти функции пытаются провести преобразование и если оно не удается, возвращают NULL вместо возникновения ошибки.
Обновленный код
Вот пример, как можно изменить вашу процедуру:
-- Используйте TRY_CONVERT для обработки возможных ошибок преобразования
FETCH NEXT FROM cur INTO
@Client_ID, @Client_Number, @Client_Name, @Matter_ID, @Matter_Number, @Matter_Name,
@AttyID, @PrimaryTpkr, @OrigTpkr, @SecondaryTpkr, @MatterStatus, @AreaOfLaw,
@Date_Created, @Closed_Date, @LastBilledDate, @LastBilledBy, @EthicalWall, @LegalHold;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Проверка дат на корректность
IF @Date_Created IS NULL OR @Closed_Date IS NULL OR @LastBilledDate IS NULL
OR TRY_CONVERT(DATETIME, @Date_Created) IS NULL
OR TRY_CONVERT(DATETIME, @Closed_Date) IS NULL
OR TRY_CONVERT(DATETIME, @LastBilledDate) IS NULL
BEGIN
INSERT INTO @RejectedRows (Client_Number, Matter_Number, ErrorMessage)
VALUES (@Client_Number, @Matter_Number, 'Неверный формат даты');
END
ELSE
BEGIN
MERGE INTO dbo.ClientMatterInfo_New AS target
USING (SELECT ...) AS source
ON ...
WHEN MATCHED THEN ...
WHEN NOT MATCHED BY TARGET THEN ...
END
END TRY
BEGIN CATCH
INSERT INTO @RejectedRows (Client_Number, Matter_Number, ErrorMessage)
VALUES (@Client_Number, @Matter_Number, ERROR_MESSAGE());
END CATCH;
FETCH NEXT FROM cur INTO
@Client_ID, @Client_Number, @Client_Name, @Matter_ID, @Matter_Number, @Matter_Name,
@AttyID, @PrimaryTpkr, @OrigTpkr, @SecondaryTpkr, @MatterStatus, @AreaOfLaw,
@Date_Created, @Closed_Date, @LastBilledDate, @LastBilledBy, @EthicalWall, @LegalHold;
ENDЗаключение
Внесение вышеуказанных изменений в вашу хранимую процедуру позволит избежать прерывания выполнения из-за ошибок преобразования дат. Вместо этого ошибка будет регистрироваться в таблице @RejectedRows, что даст вам возможность увидеть все строки с ошибками после завершения исполнения процедуры.
Если у вас возникнут дополнительные вопросы или потребуется дальнейшая помощь, не стесняйтесь обращаться.