Inquiridor
Procedure com vários comandos.

Pergunta
-
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
- Editado Adriano Zanini segunda-feira, 20 de fevereiro de 2012 18:34 formatação do script SQL
Todas as Respostas
-
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: fabrizzio.antoniaci@gmail.com- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 27 de fevereiro de 2012 00:13
-
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.
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 27 de fevereiro de 2012 00:12
-
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
- Sugerido como Resposta Junior Galvão - MVPMVP quinta-feira, 29 de março de 2012 16:58
-
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]
- Sugerido como Resposta Junior Galvão - MVPMVP quinta-feira, 29 de março de 2012 16:58
-
-- 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.- Sugerido como Resposta Junior Galvão - MVPMVP quinta-feira, 29 de março de 2012 16:58
-
(...)
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.
-
-
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: