SQL, cursor, overtake, add two entries instead of one

[Bogopo]

I'm trying to reset the original data by the courseor and make any changes for each record. Here's the request:

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 request for a courseor returns 6 lines (6 selected for example), i.e. all ID unique, nothing is retracted. Further, depending on certain conditions, action is taken, please draw attention to the two clusters of action (the comments called Block#1 and Block#2), which are strange.

Once a request is made for something, too important, not always on the same record, one on the other, or two on the record, the data lines shall be inserted, i.e. two rows shall be inserted with all the data except the auto-increment identifier repeated! And if you take action and try it on this one-on-one (i.e. a one-time cycle) it's all going to be great! That's the evil power. I don't know why this is happening, tell anyone what's wrong or where to find a solution?

P.S. Triggers are not available on the Attachments table. Lines shall always be inserted after all actions, i.e. the first condition being fulfilled, line 1 shall be inserted, then line 2 shall be inserted on the second condition, followed by line 3, and in the case of the rearing of the line, it shall be inserted at the latest, i.e., line 4 of the same line 1 (or line 2 when it is)

[courtlanda]

With help Mike. We have managed to find a solution to this problem! This is where I'm gonna put it in a little bit more detail. Okay, let's go. Result of the request for the courseor:

...however there's no duplication of identifiers.

With Mike(thanks), one of the fields (Node) recorded id notes in the bulkhead, as a result:

In the first record, it's fine, we're not gonna look at it. Second entry on id = 14308060, personID = 414674754. As a result, there was a reversal (Block #1), but in field Node, we see that at the end, the identifier on the next order of the!

The following is the result of the following:

It's okay.

Next. ♪ Fifth entry id = 148362023, personID = 15300018501113. As a result, a reversal took place (Block #2), again in field Node, the identifier of the next record

Below is the result of the following recording:

So all this has led to the idea that the variables remain the same if the installation returns NULL, although the variable is reverted in each cycle. Now watch what happens: 1. The second record is processed, as it has an active request for 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

@ID_zapros identifier 150118746, identifier completes everything as necessary, adds exactly as many records as it takes, the last recording, which is yet to be duplicated!

2. The third record is being processed. This record does not have an active request, so the following phrase returns NULL.

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

But the variable @ID_zapros does not record NULL as I anticipated, but remains the previous meaning! That's where the dog's buried. And it turns out that when the third record is processed, there's another recording of the previous record. ♪

With 5 and 6 recordings, it's the same thing, but at another stage... ♪

I thought that since the variable was declared inside the cycle, every announcement would record NULL, it was also wrong that when the variable was installed, if the result was returned to NULL, then the NULL would be recorded in the variable, it wasn't. ♪ ♪

The decision is quite simple, compelling the variable by force, and I did so:

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'm sorry for a pretty big statement, but for the first time I'm here, maybe I'm doing something wrong. Thank you again, everyone who responded! I hope it'll help someone to stay on the same grabs.