I try using the cursor to sort through the source data and make certain changes for each record. Here is the request itself:

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

The query for the cursor returns 6 lines (6 are selected for the example), that is, all IDs are unique, nothing is duplicated. Further, depending on certain conditions, certain actions are carried out, please pay attention to two blocks of actions (called Block # 1 and Block # 2 in the comments), they behave strangely.

After fulfilling a request for some (it is also important here, not always on the same record, it happens then on one then another, or even two) records are backed up with data lines, that is, two lines are inserted that have all the data except the auto-increment identifier is repeated! And if you roll back the actions and try only on this record on one (that is, run a cycle for one record) everything ends perfectly! These are the evil electrical forces. It is not clear why this is happening, tell someone what is wrong, or in which direction to look for a solution?

PS Triggers to insert data on the table Attachments no. Lines are always inserted after all actions, that is, let's say the first condition is fulfilled, line 1 is inserted, then line 2 is inserted by the second condition, then line 3, and if the line is doubled, then it is inserted most recently, that is, after line 3 line 4 is identical to line 1 (or line 2 when as)

  • You would indicate in tags and in the question text which SQL you are using. It looks like MS Sql server or Sybase but a full-time fortune teller is on vacation - Mike
  • one
    I apologize, the first question on this site. Used MS SQL Server - Chelfree
  • And we didn’t try to fetch some info from it right after the fetch into some kind of test table, to see how many rows it actually returned. Once upon a time, when I was working with Sybase, the cursors behaved unstably if you changed the table from which they were reading. The problem was solved by adding FOR UPDATE to the cursor query. But I never encountered MS SQL, so just a guess - Mike
  • I tried, put the record at the beginning after fetch and at the end after fetch, as a result only the first record was recorded at the beginning, and 2,3,4,5,6 was recorded at the end and the 6th again, for some reason at the end two sixths were recorded, but Under this record there was no zadvoenie, zadvoenie occurred on the 5th line ... - Chelfree
  • one
    Not, then the cursor behaves correctly. Two 6ths it is normal, he then leaves the cycle when he rises up and checks the condition. But what is the matter then is absolutely not clear. the entries in these blocks1-2 are clearly different in some kind of numbers ... Your cursor does not select 2 entries with 1 presonid you then verify and paste something on it. And you don’t have a particular field there, like a comment to insert at.id there at the same time the original record from the cursor ... - Mike

2 answers 2

Mike was able to find a solution to this problem! Here I will explain everything in more detail, suddenly someone will help. So, let's begin. Result of the query for the cursor:

Cursor query result

... as you can see there is no duplication of identifiers

With the help of Mike (thank you very much), I wrote down the search entries in one of the fields (Node), as a result I got the following:

In the first entry, everything is fine, we will not consider it. The second entry in the list is id = 14308060, personID = 414674754. As a result, a duplication occurred (Block # 1), but in the Node field we see that the identifier of the next record was recorded at the end !!!

Second cursor entry

The following is the result of the following entry: Third cursor entry

... everything is fine

Further .. The fifth entry is id = 148362023, personID = 15300000018501113. As a result, the duplication took place (Block # 2), again in the Node field the identifier of the next entry

Fifth cursor entry

Below is the result of the following entry in which everything is exactly:

Sixth cursor record

So, this all suggested that the values ​​in the variables remain the same if the installation returns NULL, although in each cycle the variable is declared again. Now we are looking at what is happening: 1. The second record is being processed, since it has an active query, then the following expression:

 DECLARE @ID_zapros BIGINT SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID AND endDate IS NULL AND attachmentStatusID != 2 AND id != @ID

writes the identifier 150118746 to the @ID_zapros variable, then everything runs as it should, added exactly as many entries as needed, the last entry that is duplicated is not yet available!

  1. Next, the third record is processed. This record does not have an active query, so the following expression returns NULL

    SELECT @ID_zapros = id FROM Attachments WHERE personID = @personID AND endDate IS NULL AND attachmentStatusID! = 2 AND id! = @ID

but! the @ID_zapros variable is not written NULL (as I assumed), but the previous value remains! This is where the dog is buried. And it turns out that when processing the third record, one more record is added with data about the previous record ...

with 5th and 6th notes, all is the same, only at a different stage ...

I thought that since the variable is declared inside the loop, then every time it is declared, it will be written NULL, it was also wrong that when setting the variable, if the result returns NULL, then NULL will be written to the variable, it turned out not at all ...

The solution is quite simple, forcibly resetting a variable, I did this:

 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

I apologize for a rather large statement, but for the first time I am here, maybe I’m doing something wrong, forgive me! Thanks again to everyone who responded! I hope this will help someone not to run into the same rake)

  • one
    That's just a joke, that is a statement when setting a variable, if the result returns NULL is wrong. When the request returned a single row with a NULL value, it returned NULL, and when the query could not select a single row, it did not return anything , but did not return NULL . And when he did not return anything, the variable does not change. Write similar queries in the form select max(id) from ... then it always returns a single string, which will be NULL if no matching data is found. - Mike
  • Thanks again, select max(id) from ... also works fine, did not know about it. Yes, I may not have completely figured out the problem, but now everything has finally become clear after Google, here you have the answer in 4 point stackoverflow . - Chelfree
  • I have already forgotten about such problems long ago, only after reading your post I remembered I have not contacted Sybase for a long time, and I haven’t dealt with MS Sql’s successor And everything is simple in Oracle - such a string did not return anything - the script crashes with an error :) And without max or exception handling - no way. - Mike

The code is not the most obvious, having no data is difficult to understand what is happening. Alternatively, for debugging, you can try to add output inserted.* For all insert blocks, which will give you the opportunity to see in what order and what data was inserted.

Example of output block:

 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

Perhaps here:

 SELECT @ID_zapros = id FROM Attachments WHERE ...

either here

 SELECT @ID_prikrep = id FROM Attachments WHERE ...

as the cursor moves, not what is expected is selected.

  • And you were right about the fact that not only what I expected is written to these variables, thanks! PS It turns out my reputation does not allow me to like the answers yet, unfortunately. - Chelfree