Update data by cursor in another table

Question

Update data by cursor in another table

Navigation

#1 by (0 votes)

0

I made the cursor below to update the contacts. It runs successfully, however it does not provide the expected result.

DECLARE @sequencia int,
@codigoOld int,
@telefoneOld nvarchar(5),
@timestampOld datetime, 
@vetorOld int,
@codigoNew int,
@telefoneNew nvarchar(5),
@timestampNew datetime,
@vetorNew int,
@codigo INT ,
@telefone INT; 

DECLARE CONFLITO_CURSOR CURSOR FOR SELECT sequencia,codigoOld,
telefoneOld,timestampOld,vetorOld,codigoNew,telefoneNew,timestampNew,
vetorNew FROM registoOperacoes ORDER BY sequencia ASC 

OPEN CONFLITO_CURSOR

FETCH NEXT FROM CONFLITO_CURSOR INTO @sequencia,@codigoOld,@telefoneOld,
@timestampOld,@vetorOld,@codigoNew,@telefoneNew,@timestampNew,@vetorNew

WHILE (@@FETCH_STATUS=0)
 begin 

    DECLARE SERVIDOR_CURSOR CURSOR FOR SELECT codigo,telefone FROM servidor , registoOperacoes WHERE servidor.codigo=@codigoNew
    OPEN SERVIDOR_CURSOR
    FETCH NEXT FROM SERVIDOR_CURSOR INTO  @codigo,@telefone

    WHILE (@@FETCH_STATUS=0)
    BEGIN 
        IF @timestampOld<@timestampNew
        print N'Código: '+CAST(@codigo AS NVARCHAR(10))+' Telefone: '+CAST(@telefone AS NVARCHAR(15));
        UPDATE servidor 
        SET codigo=@codigo,telefone=@telefone
        WHERE codigo=@codigo

        FETCH NEXT FROM SERVIDOR_CURSOR INTO  @codigo,@telefone
        END
        CLOSE SERVIDOR_CURSOR
        DEALLOCATE SERVIDOR_CURSOR 
FETCH NEXT FROM CONFLITO_CURSOR INTO @sequencia,@codigoOld,@telefoneOld,@timestampOld,@vetorOld,@codigoNew,@telefoneNew,@timestampNew,@vetorNew
END     
CLOSE CONFLITO_CURSOR
DEALLOCATE CONFLITO_CURSOR

asked by anonymous 23.02.2018 / 01:33

1 answer

How do I do a POST + Redirect with Angular? How to upload in php

score 0 · Answer 1

The problem with your script is that every time it is run from FETCH , the value of @@FETCH_STATUS is updated, and this makes it impossible to use nested cursors.

The solution is to assign the value of @@FETCH_STATUS to a variable, and use it to check if CURSOR still has data to read. In your code it would look like this:

DECLARE @FETCH_PRIMEIRO_CURSOR int
DECLARE @FETCH_SEGUNDO_CURSOR int

FETCH NEXT FROM CONFLITO_CURSOR INTO @sequencia,@codigoOld,@telefoneOld,
@timestampOld,@vetorOld,@codigoNew,@telefoneNew,@timestampNew,@vetorNew

-- AQUI PEGO O STATUS DO PRIMEIRO CURSOR
SET @FETCH_PRIMEIRO_CURSOR = @@FETCH_STATUS


WHILE (@@FETCH_PRIMEIRO_CURSOR=0)
 BEGIN 

    DECLARE SERVIDOR_CURSOR CURSOR FOR SELECT codigo,telefone FROM servidor , registoOperacoes WHERE servidor.codigo=@codigoNew
    OPEN SERVIDOR_CURSOR
    FETCH NEXT FROM SERVIDOR_CURSOR INTO  @codigo,@telefone

    -- AQUI PEGO O STATUS DO SEGUNDO CURSOR
    SET @FETCH_SEGUNDO_CURSOR = @@FETCH_STATUS

    WHILE (@@FETCH_SEGUNDO_CURSOR=0)
    BEGIN 
        IF @timestampOld<@timestampNew
        print N'Código: '+CAST(@codigo AS NVARCHAR(10))+' Telefone: '+CAST(@telefone AS NVARCHAR(15));
        UPDATE servidor 
        SET codigo=@codigo,telefone=@telefone
        WHERE codigo=@codigo

        FETCH NEXT FROM SERVIDOR_CURSOR INTO  @codigo,@telefone
        -- AQUI PEGO O STATUS DO SEGUNDO CURSOR
        SET @FETCH_SEGUNDO_CURSOR = @@FETCH_STATUS
    END

    CLOSE SERVIDOR_CURSOR
    DEALLOCATE SERVIDOR_CURSOR 

    FETCH NEXT FROM CONFLITO_CURSOR INTO @sequencia,@codigoOld,@telefoneOld,@timestampOld,@vetorOld,@codigoNew,@telefoneNew,@timestampNew,@vetorNew
    -- AQUI PEGO O STATUS DO PRIMEIRO CURSOR
    SET @FETCH_PRIMEIRO_CURSOR = @@FETCH_STATUS
END