Procedure com vários comandos.

Question

Tenho um problema...

E gostaria de uma orientação dos mais experientes em SQL Server (2008 R2)...

Percebo que uma procedure com varios comandos nao executam de forma eficaz.
Devo estar esquecendo de algo... Devo estar errando em algo.

Por exemplo, uma mesma procedure pode conter varios blocos de comandos:
- INSERT...
- UPDATE...
- DELETE...
- MERGE...
- e assim por diante...

No Firebird existe um comando chamado Commit Work; que voce executa no final de cada bloco de comando antes de passar para o proximo na mesma procedure. o Commit Work grava fisicamente os processos. Permitindo criar uma unica procedures com diversos comandos.

Para contornar meu problema no SQL Server, ha casos que estou tendo que criar uma Procedure para cada bloco de comandos.
Se eu continuar desse jeito, ao final do desenvolvimento meu sistema E.R.P. poderá vir a ter mais de 200 procudures.

Abaixo esta um exemplo de procedure que não executa de forma eficaz (e tive que dividir cada bloco de comando em procedures diferentes):

Seria o caso de usar "BEGIN TRANSACTION"? Ou existe outra forma?

CREATE PROCEDURE [DBO].[SPVENDA_PEDIDOAPROVAR]  (
    @NEMPRESA INTEGER,
    @NPEDIDO INTEGER,
    @ACAO VARCHAR(10)
    )
AS
BEGIN

   IF @ACAO='APROVAR'
   BEGIN
       UPDATE PEDIDOS  SET APROVADO='S'
       WHERE
          EMPRESA=@NEMPRESA
       AND
          CODIGO=@NPEDIDO
   END;


   IF @ACAO='DESAPROVAR'
   BEGIN
       UPDATE PEDIDOS  SET APROVADO='N'
       WHERE
          EMPRESA=@NEMPRESA
       AND
          CODIGO=@NPEDIDO
   END;

   ------------------------------------------------------------------------
   -- ATUALIZAR TABELA MOVIMENTAÇÃO DE PEDIDOS (PEDIDOS_MOVIMENTO)
   ------------------------------------------------------------------------
   MERGE
      PEDIDOS_MOVIMENTO AS DESTINO
   USING
      ( SELECT *  FROM PEDIDOS_ITENS_GRADE
        WHERE EMPRESA=@NEMPRESA AND CODIGO=@NPEDIDO AND APROVADO='S' AND COR_ID>0 AND TAMANHO_ID>0 ) AS ORIGEM
   ON
      (DESTINO.EMPRESA = ORIGEM.EMPRESA
       AND
       DESTINO.CODIGO = ORIGEM.CODIGO
       AND
       DESTINO.REFERENCIA = ORIGEM.REFERENCIA
       AND
       DESTINO.COR_ID = ORIGEM.COR_ID
       AND
       DESTINO.TAMANHO_ID = ORIGEM.TAMANHO_ID
        )
         

   WHEN NOT MATCHED BY TARGET THEN
            INSERT (
                     EMPRESA
                    ,CODIGO
                    ,ORDEMPRODUCAO
                    ,REFERENCIA
                    ,APROVADO
                    ,IDREFERENCIA
                    ,COR_ID
                    ,COR
                    ,TAMANHO_ID
                    ,TAMANHO
                    ,ORDEM_GRADE
                    ,CELULA_COL
                    ,COLUNA_ROW
                    ,QTDE_PECAS
                    ,QTDE_ENTREGUE
                    ,QTDE_RESTANTE
                    ,VALOR_UNIT

                  )
            VALUES (
                     EMPRESA
                    ,CODIGO
                    ,0
                    ,REFERENCIA
                    ,APROVADO
                    ,''
                    ,COR_ID
                    ,COR
                    ,TAMANHO_ID
                    ,TAMANHO
                    ,ORDEM_GRADE
                    ,CELULA_COL
                    ,COLUNA_ROW
                    ,QUANTIDADE
                    ,0             -- ESTA COLUNA SERA ATUALIZADA POR OUTROS COMANDOS
                    ,QUANTIDADE    -- ESTA COLUNA SERA ATUALIZADA POR OUTROS COMANDOS
                    ,VALOR_UNIT

            )


   WHEN MATCHED THEN
      UPDATE  SET
         DESTINO.APROVADO=ORIGEM.APROVADO,
         DESTINO.QTDE_PECAS=ORIGEM.QUANTIDADE,
         DESTINO.QTDE_RESTANTE=(DESTINO.QTDE_PECAS-DESTINO.QTDE_RESTANTE),
         DESTINO.VALOR_UNIT=ORIGEM.VALOR_UNIT


   WHEN NOT MATCHED BY SOURCE AND (DESTINO.APROVADO='N' )  THEN
      DELETE
      
   OUTPUT $ACTION, INSERTED.*,    DELETED.*;


   ------------------------------------------------------------------------
   -- NÃO PERMITIR VALORES NULOS
   ------------------------------------------------------------------------

   UPDATE PEDIDOS_MOVIMENTO SET QTDE_PECAS=0
   WHERE
      (QTDE_PECAS IS NULL) OR (QTDE_PECAS < 1);

   UPDATE PEDIDOS_MOVIMENTO SET QTDE_ENTREGUE=0
   WHERE
      (QTDE_ENTREGUE IS NULL) OR (QTDE_ENTREGUE < 1);

   UPDATE PEDIDOS_MOVIMENTO SET QTDE_RESTANTE=0
   WHERE
      (QTDE_RESTANTE IS NULL) OR (QTDE_RESTANTE < 1);


   ------------------------------------------------------------------------
   -- ATUALIZAR CAMPOS
   ------------------------------------------------------------------------
   UPDATE PEDIDOS_MOVIMENTO SET
      EMPRESA      =  ( SELECT PEDIDOS.EMPRESA     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
      DATA         =  ( SELECT PEDIDOS.DATA        FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
      CLIENTE      =  ( SELECT PEDIDOS.CLIENTE     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
      VENDEDOR     =  ( SELECT PEDIDOS.VENDEDOR    FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO )

   WHERE
      PEDIDOS_MOVIMENTO.EMPRESA=@NEMPRESA
   AND
      PEDIDOS_MOVIMENTO.CODIGO=PEDIDOS_MOVIMENTO.CODIGO;


   ------------------------------------------------------------------------
   -- ATUALIZAR TABELA MOVIMENTAÇÃO DE PRODUTOS (MOVIMENTOS_PRODUTOS)
   ------------------------------------------------------------------------
   MERGE
      MOVIMENTOS_PRODUTOS AS DESTINO
   USING
      ( SELECT *  FROM PEDIDOS_MOVIMENTO
        WHERE EMPRESA=@NEMPRESA AND APROVADO='S'  ) AS ORIGEM
   ON
      (DESTINO.EMPRESA = ORIGEM.EMPRESA
       AND
       DESTINO.REFERENCIA = ORIGEM.REFERENCIA
       AND
       DESTINO.COR_ID = ORIGEM.COR_ID
       AND
       DESTINO.TAMANHO_ID = ORIGEM.TAMANHO_ID
       AND
       ORIGEM.APROVADO='S'  )

   WHEN NOT MATCHED BY TARGET THEN
            INSERT (
                     EMPRESA
                    ,APROVADO
                    ,DATAMOVIMENTO
                    ,REFERENCIA
                    ,FASEATUAL
                    ,CELULAATUAL
                    ,MAQUINAATUAL
                    ,OPERADORATUAL
                    ,PROCESSOATUAL
                    ,TURNOATUAL
                    ,DEFEITOATUAL
                    ,QUALIDADEATUAL
                    ,COR_ID
                    ,COR
                    ,TAMANHO_ID
                    ,TAMANHO
                    ,ORDEM_GRADE
                    ,QTDE_VENDIDO
                    ,QTDE_DEMANDA
                    ,QTDE_PRODUZIR
                    ,QTDE_PRODUZIDO
                    ,QTDE_PRODUZINDO
                    ,QTDE_FISICO
                    ,QTDE_ESTOQUE
                    ,QTDE_PERDAS
                    ,QTDE_DEFEITOS                    
                  )
            VALUES (
                     EMPRESA
                    ,APROVADO
                    ,GETDATE()
                    ,REFERENCIA
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,COR_ID
                    ,COR
                    ,TAMANHO_ID
                    ,TAMANHO
                    ,ORDEM_GRADE
                    ,QTDE_PECAS
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  
                    ,0  

            )


   WHEN MATCHED THEN
      UPDATE  SET
         DESTINO.QTDE_VENDIDO=ORIGEM.QTDE_PECAS


   WHEN NOT MATCHED BY SOURCE  AND (DESTINO.QTDE_PRODUZIR<1)   THEN
      DELETE
      
   OUTPUT $ACTION, INSERTED.*,    DELETED.*;



   -- ***************************************************************
   -- ***             ATUALIZA MOVIMENTAÇÃO (PRODUZIR)         ******
   -- ***************************************************************
   UPDATE MOVIMENTOS_PRODUTOS   SET MOVIMENTOS_PRODUTOS.QTDE_PRODUZIR=COALESCE(( SELECT SUM(QTDE_TOTAL) AS QTDE_TOTAL FROM PCP_ORDEMPRODUCAO_MOVIMENTO
                                                                     WHERE
                                                                        PCP_ORDEMPRODUCAO_MOVIMENTO.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
                                                                     AND
                                                                        PCP_ORDEMPRODUCAO_MOVIMENTO.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
                                                                     AND
                                                                        PCP_ORDEMPRODUCAO_MOVIMENTO.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
                                                                     AND
                                                                        PCP_ORDEMPRODUCAO_MOVIMENTO.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID
                                                                     AND 
                                                                        PCP_ORDEMPRODUCAO_MOVIMENTO.APROVADO='S'  ) ,0)
   WHERE
      MOVIMENTOS_PRODUTOS.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
   AND
      MOVIMENTOS_PRODUTOS.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
   AND
      MOVIMENTOS_PRODUTOS.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
   AND
      MOVIMENTOS_PRODUTOS.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID;
   

   -- ***************************************************************
   -- ***             ATUALIZA MOVIMENTAÇÃO (VENDIDOS)         ******
   -- ***************************************************************
   UPDATE MOVIMENTOS_PRODUTOS   SET MOVIMENTOS_PRODUTOS.QTDE_VENDIDO=COALESCE(( SELECT SUM(QTDE_PECAS) AS QTDE_PECAS FROM PEDIDOS_MOVIMENTO
                                                                     WHERE
                                                                        PEDIDOS_MOVIMENTO.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
                                                                     AND
                                                                        PEDIDOS_MOVIMENTO.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
                                                                     AND
                                                                        PEDIDOS_MOVIMENTO.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
                                                                     AND
                                                                        PEDIDOS_MOVIMENTO.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID
                                                                     AND 
                                                                        PEDIDOS_MOVIMENTO.APROVADO='S'  ) ,0)
   WHERE
      MOVIMENTOS_PRODUTOS.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
   AND
      MOVIMENTOS_PRODUTOS.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
   AND
      MOVIMENTOS_PRODUTOS.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
   AND
      MOVIMENTOS_PRODUTOS.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID;
   
   
END;

 x

Answer 1

Andriano,

No SQL Server voce pode utilizar o SAVE TRANSACTION, exemplos:

CREATE

PROCEDURE dbo.TesteSavePoint

AS

BEGIN

BEGIN

TRAN a

INSERT

INTO Teste SELECT NEWID()

SAVE

TRANSACTION a

BEGIN

TRAN b

INSERT

Teste SELECT NEWID()

SAVE

TRANSACTION b

END

Maiores informações: http://msdn.microsoft.com/en-us/library/ms188378.aspx

---

Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Blog Empresa: www.tripletech.com.br/blog
Twitter: @FabrizzioCaputo
Email: [e-mail]

Answer 2

vc pode ter uma procedure genérica ou procedures especificas, mas quanto mais genérica mais difícil será para o MSSQL criar/manter um plano de execução otimizado e com isso problemas de desempenho podem aparecer.

sobre comandos de controle de transação, todo banco de dados possue os comandos semelhantes e podem ser usados.

vc citou o Commit Work, no MSSQL vc também tem o Commit Tran.

eu não recomendo a criação de procedures genéricas e para um ERP ter várias procedures é algo normal.

Answer 3

Oi,

Comparando com o que citou sobre o firebird, pode ser necessário um begin transaction/commit transaction.

Porém o ponto principal da questão é o que você citou em "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

O que você quer dizer com não executar de forma eficaz ?

É difícil indicar a solução para o problema, nos diga primeiramente qual foi o exato problema que identificou na execução da procedure.

Abs,

Dennes
****** Evento : Inscreva-se já para o SQL Saturday #127 no Rio : http://www.sqlsaturday.com/127/eventhome.aspx - vagas limitadas !

 

---

Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

Answer 4

Adriana,

Basicamente uma Stored Procedure, é um recurso criado no SQL Server com objetivo de automatizar, acelerar e melhorar todo processo de execução, desenvolvimento e administração de funcionalidades que podem demandar processamento e complexidade de execução.

Você esta dizendo que Stored Procedures com diversos comandos se torna lenta, eu particularmente já desenvolvi e trabalhei com Stored Procedure de 150 á 2000 linhas e esta análise da quantidade de comandos nunca me chamou a atenção.

Vale ressaltar que quanto mais executamos a mesma Stored Procedure, mais dinâmica e rápida a mesma é analisada e processada.

Eu concordo com as observações do Dennes, acredito que você poderia esclarecer um pouco mais sobre as suas percepções para que possamos ajudar.

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

Answer 5

-- Algumas mudanças que vão ajudar a melhorar a execução de procedure pelo SQL
1) Substituir @ACAO VARCHAR(10) por @APROVADO CHAR(1) -- Somente S ou N
   e Substituir "IF @ACAO='APROVAR' ...." e "IF @ACAO='DESAPROVAR'..." por um UPDATE só.
   Ex.:
    UPDATE PEDIDOS  SET APROVADO=@APROVADO
    WHERE
          EMPRESA=@NEMPRESA
       AND
          CODIGO=@NPEDIDO


Obs.: São dois Comandos (IF) a menos para executar.


3) 
   Substituir vários SELECTs da mesma tabela na mesma condição por somente um select.
   Os campos diferentes podem ser armazenados em variáveis.
   "UPDATE PEDIDOS_MOVIMENTO SET
      EMPRESA      =  ( SELECT PEDIDOS.EMPRESA     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ) ..."
   
   Ex.: 
   DECLARE  
@EMPRESA [TIPO],
@DATA [TIPO],
@CLIENTE [TIPO],
@VENDEDOR [TIPO];


   SELECT
@EMPRESA = EMPRESA,
@DATA = DATA,
@CLIENTE = CLIENTE,
@VENDEDOR = VENDEDOR
   FROM PEDIDOS 
   WHERE  PEDIDOS.CODIGO=@PEDIDOS_CODIGO; -- Variável fictícia
   
   UPDATE PEDIDOS_MOVIMENTO SET
      EMPRESA      =  @EMPRESA,
      DATA         =  @DATA,
      CLIENTE      =  @CLIENTE,
      VENDEDOR     =  @VENDEDOR
   WHERE
      PEDIDOS_MOVIMENTO.EMPRESA=@NEMPRESA
   AND
      PEDIDOS_MOVIMENTO.CODIGO=@PEDIDOS_CODIGO; -- Variável fictícia


Obs.: Notei que você está comparando [CAMPO] = [CAMPO] no WHERE do "UPDATE PEDIDOS_MOVIMENTO SET ...". 
Essa condição será sempre verdadeira, já que é como comparar 1=1.
Não seria atualizar apenas um pedido pelo código? Se for, primeiro é necessário obter o código do pedido.

Answer 6

(...)

Porém o ponto principal da questão é o que você citou em "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

O que você quer dizer com não executar de forma eficaz ?

É difícil indicar a solução para o problema, nos diga primeiramente qual foi o exato problema que identificou na execução da procedure.

(a todos dsculpem a demora em responder, eu havia perdido a senha do acesso ao forum e não conseguia responder )

Prezado Dennes,

no caso de não executar de forma eficaz, quero dizer que nem todos os comando na mesma procedures (no exemplo inicial) são executados.

Exemplo: Como voce pode observar, eu comentei cada bloco de comando. Ocorre que alguns desses blocos, em algum momento não são executados. Simplesmente não executa (até brinco aqui, que parece coisa sobrenatural).

E esse erro ocorre PRINCIPALMENTE nos comandos UPDATE (quando preciso determinada tabela) conforme está exemplo inicial. Simplesmente não executa.

E foi o caso, criei uma procedure para cada UPDATE. Por isso até "brinquei" que daqui a pouco vou ter mais de 200 procedures só para uma mesma transação. Sendo que eu poderia ter tudo numa mesma procedure (como  estou tentando fazer).

Por isso comentei que "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

É nesse sentido.

Answer 7

Fabrizzio,

vou seguir sua indicação. E postarei aqui sobre os resultados.
Creio que possa resolver.

Obrigado pela ajuda.

Answer 8

Obs.: Notei que você está comparando [CAMPO] = [CAMPO] no WHERE do "UPDATE PEDIDOS_MOVIMENTO SET ...". 
Essa condição será sempre verdadeira, já que é como comparar 1=1.
Não seria atualizar apenas um pedido pelo código? Se for, primeiro é necessário obter o código do pedido.

Alex,

nesse caso sim...  de certa forma voce tem toda razão.

No entanto, foi uma forma que descobri (em estudos pessoais) anos atras sobre como aplicar UPDATE  sem afetar os outros registros.
É algo que funciona em qualquer banco de dados SQL.

Dessa forma fazendo ele comparar (ligar) a outra tabela cujas estruturas sejam semelhantes e com dados semelhantes sem afetar outras linhas.

E no caso em questão, eu solicitei ao SQL que atualize o campo "QTDE_VENDIDO" restringindo-se ao que solicitei.

Pode parecer um comando meio "absurdo". Mas acredite, funciona.

Veja um exemplo gráfico: