Procedure com select e update

Question

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.

Answer 1

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"

Answer 2

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?

Answer 3

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

Answer 4

Deleted

Answer 5

Deleted

Answer 6

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"

Answer 7

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.

Answer 8

Durval,

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

Muito obrigada!

Answer 9

Deleted