Пытаюсь с помощью курсора перебрать исходные данные и для каждой записи сделать те
или иные изменения. Вот сам запрос:
DECLARE @ID bigint --id attachments
DECLARE @personID BIGINT
DECLARE @territoryServiceID BIGINT
DECLARE @isAtClosed BIT
DECLARE @currentServerDate DATETIME = '2016-01-01 01:10:00.000' --this change GETDATE()
DECLARE @BeginDate DATETIME SET @BeginDate = @currentServerDate
DECLARE @periodYear INT SET @periodYear = DATEPART(YEAR,@currentServerDate) - 1
DECLARE cur cursor LOCAL STATIC
FOR
SELECT at.id, at.personID, at.territoryServiceID, ts.isClosing
FROM Attachments at
INNER JOIN Person p ON p.id = at.personID AND p.parentID IS NULL
INNER JOIN TerritoryServices ts ON ts.id = at.territoryServiceID
LEFT JOIN Attachments at2 ON at2.personID = at.personID AND at2.parentID = at.id
AND at2.attachmentStatusID IN (2,11,12)
WHERE at.attachmentStatusID = 1 AND at.causeOfAttachID = 8 AND at.endDate IS NOT NULL
AND at2.id IS NULL
AND p.id IN (15300000019296419,15300000018501113,15300000014988209,414674754,420940229,409531785)
OPEN cur
FETCH NEXT FROM cur INTO @ID, @personID, @territoryServiceID, @isAtClosed
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @personID_NVARCHAR NVARCHAR(MAX) SET @personID_NVARCHAR = CONVERT(NVARCHAR(MAX),@personID)
PRINT '1 ('+@personID_NVARCHAR+')'
IF (@isAtClosed = 1) -- if ter of CA is closing
BEGIN
-- Insert error into ErrorHandlingCampainOfAttach
DECLARE @ErrorDescr NVARCHAR(MAX) SET @ErrorDescr = 'TerId: ' + CONVERT(NVARCHAR(MAX),@territoryServiceID)
INSERT INTO [dbo].[ErrorHandlingCampainOfAttach] ([AttachmentsID],[personID],[territoryServiceID],[periodYear],[reasonError],[addDate],[description])
VALUES (@ID, @personID, @territoryServiceID, @periodYear, 1, GETDATE(), @ErrorDescr)
END
ELSE
BEGIN
DECLARE @terAt2ID BIGINT
DECLARE @isAt2Close BIT = 0
SELECT @isAt2Close = ts.isClosing, @terAt2ID = ts.id FROM Attachments at
INNER JOIN TerritoryServices ts ON ts.id = at.territoryServiceID
WHERE at.personID = @personID AND at.attachmentStatusID = 2 AND at.endDate
IS NULL
IF (@isAt2Close = 1) -- if ter of attach is closing
BEGIN
-- Insert error into ErrorHandlingCampainOfAttach
DECLARE @ErrorDescr2 NVARCHAR(MAX) SET @ErrorDescr2 = 'TerAttachId:
' + CONVERT(NVARCHAR(MAX),@terAt2ID)
INSERT INTO [dbo].[ErrorHandlingCampainOfAttach] ([AttachmentsID],[personID],[territoryServiceID],[periodYear],[reasonError],[addDate],[description])
VALUES (@ID, @personID, @territoryServiceID, @periodYear, 2, GETDATE(),
@ErrorDescr2)
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION TranName
-- Search active request
DECLARE @ID_zapros BIGINT
SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID
AND endDate IS NULL AND attachmentStatusID != 2 AND id != @ID
IF (@ID_zapros IS NOT NULL)
BEGIN
-- Canseled request
-- Block #1
-- Create cancel for active request
INSERT INTO Attachments (personID,orgHealthCareID,personAddressesID,territoryServiceID,attachmentProfileID,doctorID,
causeOfAttachID,careAtHome,senderRequestID,senderSystemID,attachmentStatusID,beginDate,endDate,parentID,userID,registratorID,
actualAttachmentID,ConflictAttachment,Node,regDate,isMigrated,isDuplicate,oldPersonID,servApplicationID,Num)
SELECT at.personID,at.orgHealthCareID,at.personAddressesID,at.territoryServiceID,at.attachmentProfileID,
at.doctorID,
8,at.careAtHome,NULL,NULL, 11, @BeginDate, @BeginDate,
at.id,
at.userID, at.registratorID, at.actualAttachmentID,
NULL,NULL,at.regDate,NULL,0,at.oldPersonID,NULL,at.Num
FROM Attachments at
WHERE at.id = @ID_zapros
-- Set endDate for active request
UPDATE Attachments SET endDate = @BeginDate WHERE id
= @ID_zapros
END
--Search active attach
DECLARE @ID_prikrep BIGINT
SELECT @ID_prikrep = id FROM Attachments WHERE personID = @personID
AND endDate IS NULL AND attachmentStatusID = 2
IF (@ID_prikrep IS NOT NULL)
BEGIN
-- Block #2
-- Insert detach
INSERT INTO Attachments (personID,orgHealthCareID,personAddressesID,territoryServiceID,attachmentProfileID,doctorID,
causeOfAttachID,careAtHome,senderRequestID,senderSystemID,attachmentStatusID,beginDate,endDate,parentID,userID,registratorID,
actualAttachmentID,ConflictAttachment,Node,regDate,isMigrated,isDuplicate,oldPersonID,servApplicationID,Num)
SELECT at.personID,at.orgHealthCareID,at.personAddressesID,at.territoryServiceID,at.attachmentProfileID,
at.doctorID,
8,at.careAtHome,NULL,NULL, 8, @BeginDate, @BeginDate,
at.id,
at.userID, at.registratorID, at.actualAttachmentID,
NULL,NULL,at.regDate,NULL,0,at.oldPersonID,NULL,at.Num
FROM Attachments at
WHERE at.id = @ID_prikrep
--Set endDate for active attach
UPDATE Attachments SET endDate = @BeginDate WHERE id
= @ID_prikrep
END
-- Attach CA
INSERT INTO Attachments (personID,orgHealthCareID,personAddressesID,territoryServiceID,attachmentProfileID,doctorID,
causeOfAttachID,careAtHome,senderRequestID,senderSystemID,attachmentStatusID,beginDate,endDate,parentID,userID,registratorID,
actualAttachmentID,ConflictAttachment,Node,regDate,isMigrated,isDuplicate,oldPersonID,servApplicationID,Num)
SELECT at.personID,at.orgHealthCareID,at.personAddressesID,at.territoryServiceID,at.attachmentProfileID,
at.doctorID,
8,at.careAtHome,NULL,NULL, 2, @BeginDate, NULL, at.id,
at.userID, at.registratorID, at.actualAttachmentID, NULL,NULL,at.regDate,NULL,0,at.oldPersonID,NULL,at.Num
FROM Attachments at
WHERE at.id = @ID
COMMIT TRANSACTION TranName
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION TranName
-- Insert error into ErrorHandlingCampainOfAttach
INSERT INTO [dbo].[ErrorHandlingCampainOfAttach] ([AttachmentsID],[personID],[territoryServiceID],[periodYear],[reasonError],[addDate],[description])
VALUES (@ID, @personID, @territoryServiceID, @periodYear, 3,
GETDATE(),ERROR_MESSAGE())
END CATCH
END
END
FETCH NEXT FROM cur INTO @ID, @personID, @territoryServiceID, @isAtClosed
END
CLOSE cur
DEALLOCATE cur
Запрос для курсора возвращает 6 строк(6 выбраны для примера), то есть все ID уникальные,
ничего не задваивается. Далее в зависимости от определенных условий производятся те
или иные действия, прошу обратить внимание на два блока действий (в комментариях называется
Block#1 и Block#2), именно они ведут себя странно.
После выполнения запроса на какой-нибудь (тут тоже важно, не всегда на одной и той-же
записи, бывает то на одной то на другой, а то и на двух) записи задваиваются строки
с данными, то есть вставляется две строки у которых все данные кроме автоинкрементного
идентификатора повторяются! Причем если откатить действия и попробовать только на этой
записи на одной (то есть цикл прогнать для одной записи) все завершается отлично! Вот
такие-вот злые электрические силы. Непонятно почему так происходит, подскажите кто-нибудь
что не так, или в каком направлении искать решение?
P.S. триггеров на вставку данных на таблице Attachments нету. Строки всегда вставляются
после всех действия, то есть допустим первое условие выполняется, вставляется строка
1, затем по второму условию вставляется строка 2, затем строка 3, и в случае если происходит
задвоение строки, то она вставляется самой последней, то есть после строки 3 вставляется
строка 4 идентичная строке 1 (или строке 2 когда как)
Ответы
Ответ 1
С помощью Mike удалось найти решение данной проблемы! Тут я изложу все поподробнее
вдруг кому-то поможет.
Итак, начнем. Результат запроса для курсора:
... как видно никакого дублирования идентификаторов нет
С подсказкой Mike(спасибо огромное), в одно из полей (Node) записал id записи в переборе,
в результате получил следующее:
В первой записи все отлично, не будем ее рассматривать.
Вторая запись по списку id = 14308060, personID = 414674754. В результате произошло
задвоение (Block #1), но в поле Node видим, что в конце записался идентификатор следующей
по порядку записи!!!
Ниже приведен результат следующей записи:
... тут все нормально
Далее..
Пятая запись id = 148362023, personID = 15300000018501113. В результате прошло задвоение
(Block #2), опять же в поле Node идентификатор следующей записи
Ниже результат следующей записи в которой все ровно:
Итак, все это навело на мысль, значения в переменных остаются прежними если в результате
установки возвращается NULL, хотя в каждом цикле переменная объявляется заново.
Теперь смотрим что происходит:
1. Выполняется обработка второй записи, так как у нее есть активный запрос то следующее
выражение:
DECLARE @ID_zapros BIGINT
SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID AND endDate IS
NULL AND attachmentStatusID != 2 AND id != @ID
записывает в переменную @ID_zapros идентификатор 150118746, далее выполняется все
как нужно, добавляется ровно столько записей сколько нужно, последней записи, которая
дублируется еще пока нет!
Далее выполняется обработка третьей записи. У данной записи активного запроса нет,
поэтому следующее выражение возвращает NULL
SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID AND endDate IS
NULL AND attachmentStatusID != 2 AND id != @ID
но! в переменную @ID_zapros записывается не NULL(как я предполагал), а остается предыдущее
значение! Вот тут то и зарыта собака. И получается что при обработке третьей записи
добавляется еще одна запись в с данными о предыдущей записи...
с 5 и 6 записью все тоже самое, только уже на другом этапе...
Я думал что, так как переменная объявляется внутри цикла, то при каждом объявлении
в нее будет записываться NULL, также ошибался что при установки переменной, если результат
возвращает NULL, то и в переменную запишется NULL, оказалось совсем не так...
Решение довольно простое, обнулять переменную принудительно, я сделал так:
DECLARE @ID_zapros BIGINT
SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID AND endDate IS
NULL AND attachmentStatusID != 2 AND id != @ID
IF (@ID_zapros IS NOT NULL)
BEGIN
--Отказываем запрос
--Создаем отказ активному запросу
INSERT INTO Attachments (personID,orgHealthCareID,personAddressesID,territoryServiceID,attachmentProfileID,doctorID,
causeOfAttachID,careAtHome,senderRequestID,senderSystemID,attachmentStatusID,beginDate,endDate,parentID,userID,registratorID,
actualAttachmentID,ConflictAttachment,Node,regDate,isMigrated,isDuplicate,oldPersonID,servApplicationID,Num)
SELECT at.personID,at.orgHealthCareID,at.personAddressesID,at.territoryServiceID,at.attachmentProfileID,
at.doctorID,
8,at.careAtHome,NULL,NULL, 11, @BeginDate, @BeginDate,
at.id,
at.userID, at.registratorID, at.actualAttachmentID,
NULL,@nvar_ID,at.regDate,NULL,0,at.oldPersonID,NULL,at.Num
FROM Attachments at
WHERE at.id = @ID_zapros
--Закрываем дату активному запросу
UPDATE Attachments SET endDate = @BeginDate WHERE id = @ID_zapros
SET @ID_zapros = NULL
END
Извиняюсь за довольно большое изложения, но я впервые здесь, может что-то делаю не
так вы уж простите! Еще раз спасибо всем кто откликнулся! Надеюсь это кому-нибудь поможет
не напороться на те же грабли)
Ответ 2
Код не самый очевидный, не имея данных сложновато понять, что происходит. Как вариант,
для отладки вы можете попробовать добавить output inserted.* для всех блоков insert,
что даст вам возможность посмотреть в каком порядке и какие именно данные были вставлены.
Пример работы output блока:
declare @attachments table (id int, status_id int)
insert into @attachments (id, status_id)
output 'block #1', inserted.*
values (1, 2)
insert into @attachments (id, status_id)
output 'block #2', inserted.*
select 3, 8
Возможно, здесь:
SELECT @ID_zapros = id FROM Attachments WHERE ...
либо здесь
SELECT @ID_prikrep = id FROM Attachments WHERE ...
по мере движения курсора выбирается не то, что ожидается.
