none
Procedure com select e update RRS feed

  • Pergunta

  • Pessoal,

    Gostaria de ajuda para criar uma procedure que atualize um campo de uma tabela a depender do último status de um registro.

    Ex:

    CREATE TABLE Table (
            num_serie VARCHAR,
            status INT,
            data DATETIME,
            flag_ultimo bit
    )


    Tenho essa tabela sem ID (já está em produção). Foi criado um novo campo flag_ultimo, que deve ser 1 quando for o último registro de um num_serie.

    num_serie | status |    data    | flag_ultimo
    NS-1111   |    2   | 2014-11-28 | 0
    NS-2222   |    3   | 2014-11-28 | 1
    NS-1111   |    5   | 2014-11-28 | 0
    NS-1111   |    7   | 2014-11-30 | 1

    Eu preciso atualizar o valor do campo flag_ultimo, baseado no status em que o num_serie está. Sendo que o mesmo num_serie pode possuir vários registros com status diferentes.

    Daí pensei em criar uma procedure que pegue a última entrada de um num_serie e dê um update no flag_ultimo.

    Alguém pode me ajudar com isso? Nunca fiz uma procedure antes.







    segunda-feira, 1 de dezembro de 2014 15:11

Respostas

  • Carine,

    Segue um exemplo de criação de procedure com algumas condições como você precisa.

    Faça às adaptações de acordo com sua necessidade:

    CREATE TABLE TABELA (
    	num_serie VARCHAR(25),
    	status INT,
    	data DATETIME,
    	flag_ultimo bit
    )
    
    INSERT INTO TABELA (num_serie, STATUS, data, flag_ultimo) VALUES ('NS-1111',2,'2014-11-28',0)
    INSERT INTO TABELA VALUES ('NS-2222',3,'2014-11-28',1)
    INSERT INTO TABELA VALUES ('NS-1111',5,'2014-11-28',0)
    INSERT INTO TABELA VALUES ('NS-1111',7,'2014-11-30',1)
    GO
    
    CREATE PROC usp_atualiza_flag
      @num_serie	VARCHAR(25),
      @flag_ultimo	BIT
    AS
      BEGIN
    	DECLARE @ULTIMO_STATUS INT
    	SELECT @ULTIMO_STATUS = MAX([STATUS]) FROM TABELA WHERE num_serie = @num_serie 
    
    	IF @ULTIMO_STATUS > 0 
    	  BEGIN
    		 UPDATE TABELA SET
    		  flag_ultimo = @flag_ultimo
    		 WHERE 
    		  num_serie = @num_serie AND
    		  [STATUS] = @ULTIMO_STATUS
    	  END
      END
    GO
    
    --TESTE MUDANDO "FLAG = 0"
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    
    EXEC usp_atualiza_flag 	'NS-1111', 0
    
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    GO
    
    --TESTE MUDANDO "FLAG = 1"
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    
    EXEC usp_atualiza_flag 	'NS-1111', 1
    
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    GO

    Para maiores informações veja:

    http://msdn.microsoft.com/pt-br/library/ms190782.aspx

    http://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta Carine Monteiro terça-feira, 2 de dezembro de 2014 17:44
    segunda-feira, 1 de dezembro de 2014 16:09
    Moderador
  • Olá Durval,

    Obrigada pela resposta ágil. 

    No caso eu não qual o num_serie que será passado, pois é uma tabela com milhares de registros.

    Tem como pegar uma lista de num_series e para cada registro dessa lista dar o update?

    SELECT DISTINCT num_serie FROM tab_ficha_producao
    
    TIPO DE LOOP
    
    DECLARE @ULTIMO_STATUS INT
    
    SELECT @ULTIMO_STATUS = MAX([STATUS]) FROM TABELA 
    WHERE num_serie = @num_serie 
    
        IF @ULTIMO_STATUS > 0 
            BEGIN
    	    UPDATE TABELA SETflag_ultimo = @flag_ultimo WHERE  num_serie = @num_serie 
                AND [STATUS] = @ULTIMO_STATUS
            END
    
    ...
    END

    Tem como fazer isso?

    Carine,

    Seguindo a idéia da procedure como te indiquei, basta você adicionar um CURSOR para efetuar esta tarefa.

    Segue um exemplo de script:

    SELECT * FROM TABELA
    GO
    
    DECLARE @SERIE	VARCHAR(25)
    DECLARE cursorFlag CURSOR FOR
    	SELECT DISTINCT num_serie FROM TABELA
    OPEN cursorFlag
    FETCH NEXT FROM cursorFlag INTO @SERIE
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	EXEC usp_atualiza_flag @SERIE, 1
    
    	FETCH NEXT FROM cursorFlag INTO @SERIE
    END
    CLOSE cursorFlag ;
    DEALLOCATE cursorFlag
    
    SELECT * FROM TABELA
    GO

    Segue abaixo um print-screen como Evidência de Teste:

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução!

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta Carine Monteiro terça-feira, 2 de dezembro de 2014 17:43
    terça-feira, 2 de dezembro de 2014 12:30
    Moderador

Todas as Respostas

  • Carine,

    Segue um exemplo de criação de procedure com algumas condições como você precisa.

    Faça às adaptações de acordo com sua necessidade:

    CREATE TABLE TABELA (
    	num_serie VARCHAR(25),
    	status INT,
    	data DATETIME,
    	flag_ultimo bit
    )
    
    INSERT INTO TABELA (num_serie, STATUS, data, flag_ultimo) VALUES ('NS-1111',2,'2014-11-28',0)
    INSERT INTO TABELA VALUES ('NS-2222',3,'2014-11-28',1)
    INSERT INTO TABELA VALUES ('NS-1111',5,'2014-11-28',0)
    INSERT INTO TABELA VALUES ('NS-1111',7,'2014-11-30',1)
    GO
    
    CREATE PROC usp_atualiza_flag
      @num_serie	VARCHAR(25),
      @flag_ultimo	BIT
    AS
      BEGIN
    	DECLARE @ULTIMO_STATUS INT
    	SELECT @ULTIMO_STATUS = MAX([STATUS]) FROM TABELA WHERE num_serie = @num_serie 
    
    	IF @ULTIMO_STATUS > 0 
    	  BEGIN
    		 UPDATE TABELA SET
    		  flag_ultimo = @flag_ultimo
    		 WHERE 
    		  num_serie = @num_serie AND
    		  [STATUS] = @ULTIMO_STATUS
    	  END
      END
    GO
    
    --TESTE MUDANDO "FLAG = 0"
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    
    EXEC usp_atualiza_flag 	'NS-1111', 0
    
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    GO
    
    --TESTE MUDANDO "FLAG = 1"
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    
    EXEC usp_atualiza_flag 	'NS-1111', 1
    
    SELECT * FROM TABELA
    WHERE num_serie = 'NS-1111'
    GO

    Para maiores informações veja:

    http://msdn.microsoft.com/pt-br/library/ms190782.aspx

    http://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta Carine Monteiro terça-feira, 2 de dezembro de 2014 17:44
    segunda-feira, 1 de dezembro de 2014 16:09
    Moderador
  • Olá Durval,

    Obrigada pela resposta ágil. 

    No caso eu não qual o num_serie que será passado, pois é uma tabela com milhares de registros.

    Tem como pegar uma lista de num_series e para cada registro dessa lista dar o update?

    SELECT DISTINCT num_serie FROM tab_ficha_producao
    
    TIPO DE LOOP
    
    DECLARE @ULTIMO_STATUS INT
    
    SELECT @ULTIMO_STATUS = MAX([STATUS]) FROM TABELA 
    WHERE num_serie = @num_serie 
    
        IF @ULTIMO_STATUS > 0 
            BEGIN
    	    UPDATE TABELA SETflag_ultimo = @flag_ultimo WHERE  num_serie = @num_serie 
                AND [STATUS] = @ULTIMO_STATUS
            END
    
    ...
    END

    Tem como fazer isso?

    segunda-feira, 1 de dezembro de 2014 16:45
  • Boa tarde,

    Carine, não sei se entendi corretamente, mas talvez você consiga fazer essa atualização com o comando abaixo:

    with CTE_RN as
    (
        select
            *,
            ROW_NUMBER() OVER(PARTITION BY num_serie ORDER BY status desc) as RN
        from Tabela
    )
    
    update CTE_RN
    set flag_ultimo = case when RN = 1 then 1 else 0 end

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 1 de dezembro de 2014 17:18
  • Deleted
    segunda-feira, 1 de dezembro de 2014 19:14
  • Deleted
    segunda-feira, 1 de dezembro de 2014 20:18
  • Olá Durval,

    Obrigada pela resposta ágil. 

    No caso eu não qual o num_serie que será passado, pois é uma tabela com milhares de registros.

    Tem como pegar uma lista de num_series e para cada registro dessa lista dar o update?

    SELECT DISTINCT num_serie FROM tab_ficha_producao
    
    TIPO DE LOOP
    
    DECLARE @ULTIMO_STATUS INT
    
    SELECT @ULTIMO_STATUS = MAX([STATUS]) FROM TABELA 
    WHERE num_serie = @num_serie 
    
        IF @ULTIMO_STATUS > 0 
            BEGIN
    	    UPDATE TABELA SETflag_ultimo = @flag_ultimo WHERE  num_serie = @num_serie 
                AND [STATUS] = @ULTIMO_STATUS
            END
    
    ...
    END

    Tem como fazer isso?

    Carine,

    Seguindo a idéia da procedure como te indiquei, basta você adicionar um CURSOR para efetuar esta tarefa.

    Segue um exemplo de script:

    SELECT * FROM TABELA
    GO
    
    DECLARE @SERIE	VARCHAR(25)
    DECLARE cursorFlag CURSOR FOR
    	SELECT DISTINCT num_serie FROM TABELA
    OPEN cursorFlag
    FETCH NEXT FROM cursorFlag INTO @SERIE
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	EXEC usp_atualiza_flag @SERIE, 1
    
    	FETCH NEXT FROM cursorFlag INTO @SERIE
    END
    CLOSE cursorFlag ;
    DEALLOCATE cursorFlag
    
    SELECT * FROM TABELA
    GO

    Segue abaixo um print-screen como Evidência de Teste:

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução!

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta Carine Monteiro terça-feira, 2 de dezembro de 2014 17:43
    terça-feira, 2 de dezembro de 2014 12:30
    Moderador
  • Olá pessoal,

    Sim, José Dias. O objetivo é sinalizar o último registro como a etapa atual em que o número de série está. Geralmente não haverá esse tipo de concorrência já que é um processo sequencial, um mesmo número de série não pode estar em duas etapas diferentes e existem regras de onde ele veio e para onde ele vai. Esta validação já é feita.

    A criação dessa flag tem o objetivo de melhorar a performance do método que consulta o último status, que hoje é feito com TOP 1 e ORDER BY DESC e consome muito tempo, tempo este que é inadequado e restritivo ao processo atual.

    Como o sistema já está em produção e existem milhares de registros, se fez necessário a criação de uma procedure que consulte uma lista única de números de série existentes e a partir dessa lista que seja consulta qual o último registro deste número de série, então marcar 1(um) para a última etapa e 0(zero) para as anteriores.

    Como nunca fiz uma procedure antes, e depois de pesquisar um pouco na net, preferi pedir ajuda para este problema específico.

    Obrigada a todos, eu estou analisando todas as soluções apresentadas.

    terça-feira, 2 de dezembro de 2014 12:33
  • Durval,

    A solução proposta satisfaz a necessidade. Testei em ambiente de teste e foi bem sucedida.

    Muito obrigada!

    terça-feira, 2 de dezembro de 2014 19:13
  • Deleted
    terça-feira, 2 de dezembro de 2014 20:52