Uma Stored Procedure é um conjunto de instruções SQL pré-compiladas que são armazenadas em um banco de dados e podem ser executadas sob demanda. Essas procedimentos são úteis para executar tarefas repetitivas e evitar erros, já que eles já possuem os comandos SQL necessários para executar a tarefa.

Uma Stored Procedure pode melhorar a performance de um banco de dados, criar tarefas agendadas, reduzir o tráfego de rede, diminuir riscos, entre outros.

Termos Técnicos:

Nomeação: Cada Stored Procedure possui um nome único dentro do banco de dados, permitindo fácil referência e chamada.

Parâmetros: Pode aceitar valores de entrada (parâmetros) que podem ser utilizados dentro da própria Stored Procedure para personalizar a lógica de execução.

Reusabilidade: Uma vez criada, uma Stored Procedure pode ser chamada por vários scripts, aplicativos ou outras Stored Procedures, promovendo a reusabilidade do código.

Segurança: Stored Procedures podem ser usadas para controlar o acesso aos dados, uma vez que os usuários podem ser concedidos permissões apenas para executar a Stored Procedure em vez de acessar diretamente tabelas ou visualizações.

Desempenho: Stored Procedures são frequentemente compiladas e armazenadas em cache, o que pode resultar em melhor desempenho em comparação com consultas SQL.

Transações: Elas podem ser utilizadas para gerenciar transações, garantindo que um conjunto de operações seja executado de maneira atômica (tudo ou nada).

Cenários de Uso e Estudos de Caso para Stored Procedures:

Autenticação e Autorização:

Cenário: Um sistema de gestão de usuários em uma aplicação web.

Stored Procedures: Uma Stored Procedure para autenticar usuários, verificando as credenciais fornecidas em relação aos registros na tabela de usuários.

Uma Stored Procedure para conceder ou revogar permissões de acesso com base no perfil do usuário.

Geração de Relatórios Complexos:

Cenário: Uma empresa que necessita de relatórios detalhados sobre vendas, despesas e lucros.

Stored Procedures: Uma Stored Procedure que aceita parâmetros como data inicial e final para gerar relatórios personalizados de vendas.

Uma Stored Procedure que calcula margens de lucro com base em dados de vendas e despesas.

Manutenção de Dados Segura:

Cenário: Um sistema de e-commerce que gerencia informações de clientes e pedidos.
Stored Procedures: Uma Stored Procedure para inserir novos clientes, validando e sanitizando os dados antes da inserção na tabela.

Uma Stored Procedure para atualizar o status de um pedido, garantindo que apenas pedidos válidos possam ser alterados.

Controle de Transações Financeiras:

Cenário: Um sistema bancário que realiza transferências entre contas.
Stored Procedures: Uma Stored Procedure que aceita parâmetros como conta de origem, conta de destino e valor, executando uma transação segura de transferência de fundos.
Uma Stored Procedure para registrar transações, garantindo a consistência dos dados em caso de falha.

Notificações e Alertas:

Cenário: Uma aplicação de comércio eletrônico que envia notificações aos clientes.
Stored Procedures: Uma Stored Procedure para enviar e-mails de confirmação de pedidos após uma compra.
Uma Stored Procedure para gerar alertas quando o estoque de um produto atinge um nível crítico.

Rotinas de Manutenção Automática:

Cenário: Um banco de dados que requer rotinas de manutenção periódicas.
Stored Procedures: Uma Stored Procedure que realiza compactação e reorganização de índices para otimizar o desempenho do banco de dados.

Uma Stored Procedure para fazer backup e arquivar dados antigos, mantendo o banco de dados eficiente e gerenciável.

A imagem a cima mostra a função de um Stored Procedure

Como criar um STORED PROCEDURE?

Criar um novo procedimento armazenado é simples. Existem 3 etapas básicas:

No SQL Server Object Explorer , clique com o botão direito no nó Procedimentos armazenados em Programabilidade.

No novo modelo de procedimento armazenado, modifique a instrução CREATE PROCEDURE para incluir as tabelas, parâmetros e opções de consulta para retornar os dados desejados do STORED PROCEDURE.

Clique no botão Executar na barra de ferramentas para compilar e salvar seu procedimento armazenado .

1. Exemplo prático - Procedimento simples para inserir um registro

Suponha que você tenha uma tabela chamada 'pessoas' e deseja inserir um novo registro nessa tabela. Em vez de executar a instrução SQL completa toda vez que você deseja inserir um novo registro, você pode criar um procedimento armazenado para isso.

CREATE PROCEDURE pesquisar_pessoas (
IN criterios VARCHAR(255),
OUT nome VARCHAR(255),
OUT idade INT,
OUT email VARCHAR(255)
)
BEGIN
SELECT nome, idade, email INTO nome, idade, email FROM pessoas WHERE nome LIKE criterios;
END;

Agora você pode simplesmente executar o procedimento armazenado 'inserir_pessoa' com os valores apropriados sempre que desejar inserir um novo registro na tabela 'pessoas'.

2. Exemplo prático - Procedimento com parâmetros variáveis

Agora, suponha que você queira pesquisar registros na tabela 'pessoas' com base em um ou mais critérios de pesquisa. Você pode criar um procedimento armazenado com parâmetros variáveis para lidar com isso.

CREATE PROCEDURE pesquisar_pessoas (
IN criterios VARCHAR(255),
OUT nome VARCHAR(255),
OUT idade INT,
OUT email VARCHAR(255)
)
BEGIN
SELECT nome, idade, email INTO nome, idade, email FROM pessoas WHERE nome LIKE criterios;
END;

Agora você pode executar o procedimento armazenado 'pesquisar_pessoas' com diferentes critérios de pesquisa para encontrar registros correspondentes na tabela 'pessoas'.

3. Exemplo Prático - Procedimento com múltiplas instruções

Um procedimento armazenado pode executar várias instruções SQL em sequência. Por exemplo, você pode criar um procedimento armazenado para inserir um novo registro na tabela 'pessoas' e, em seguida, executar uma consulta para obter todos os registros da tabela 'pessoas'.

CREATE PROCEDURE inserir_e_pesquisar_pessoas (
IN nome VARCHAR(255),
IN idade INT,
IN email VARCHAR(255),
OUT resposta VARCHAR(255)
)
BEGIN
INSERT INTO pessoas (nome, idade, email) VALUES (nome, idade, email);
SELECT nome, idade, email INTO resposta FROM pessoas;
END;

Agora você pode executar o procedimento armazenado 'inserir_e_pesquisar_pessoas' para inserir um novo registro e obter todos os registros da tabela 'pessoas' em um único passo.

4. Exemplo prático - Procedimento para atualizar registros

Um procedimento armazenado pode ser usado para atualizar registros em uma tabela. Por exemplo, você pode criar um procedimento armazenado para atualizar as informações de um registro na tabela 'pessoas'.

CREATE PROCEDURE atualizar_pessoa (
IN id INT,
IN nome VARCHAR(255),
IN idade INT,
IN email VARCHAR(255)
)
BEGIN
UPDATE pessoas SET nome = nome, idade = idade, email = email WHERE id = id;
END;

Agora você pode executar o procedimento armazenado 'atualizar_pessoa' com os valores apropriados para atualizar um registro na tabela 'pessoas'.

5. Exemplo prático - Procedimento para excluir registros

Por fim, um procedimento armazenado pode ser usado para excluir registros de uma tabela. Por exemplo, você pode criar um procedimento armazenado para excluir um registro na tabela 'pessoas'.

CREATE PROCEDURE excluir_pessoa (
IN id INT
)
BEGIN
DELETE FROM pessoas WHERE id = id;
END;

Agora você pode executar o procedimento armazenado 'excluir_pessoa' com o valor apropriado para excluir um registro na tabela 'pessoas'.

Conclusão

As Stored Procedures são poderosas ferramentas no SQL Server, proporcionando organização, reusabilidade e segurança ao seu código SQL. Ao incorporar esses exemplos em sua rotina de trabalho, você poderá otimizar suas consultas e melhorar a eficiência de suas operações no banco de dados.

Johnny Willian
Nicolas Sales
Paulo Lopes

FONTE:

https://www.revealbi.io/blog/stored-procedures-embedded-analytics

https://www.youtube.com/watch?v=lHE5i7sbhhU&ab_channel=CFBCursos

https://www.techtarget.com/searchoracle/definition/stored-procedure#:~:text=A%20stored%20procedure%20is%20a,and%20shared%20by%20multiple%20programs

https://imasters.com.br/banco-de-dados/afinal-de-contas-o-que-e-uma-stored-procedure

https://leonardofonseca.com.br/2021/09/13/stored-procedures/

https://pt.wikipedia.org/wiki/Procedimento_armazenado