Como Criar e Executar Stored Procedures no SQL Server – parte 01 – 40

Criação e Execução de Stored Procedures no SQL Server – 01

Stored Procedures (Procedimentos Armazenados) são lotes (batches) de declarações SQL que podem ser executadas como uma única subrotina – ou eja, como um bloco. Um procedimento armazenado é uma sub-rotina disponível para aplicações que acessam sistemas de bancos de dados relacionais.

  • Permitem centralizar o lógica de acesso aos dados em um único local, facilitando a manutenção e otimização de código.
  • Podem ser usadas para validação de dados, controle de acesso, execução de declarações SQL complexas e outras situações.
  • Também é possível ajustar permissões de acesso aos usuários, definindo quem pode ou não executá-las.

Como criar um Procedimento Armazenado no SQL Server

A sintaxe para a criação de um procedimento armazenado no SQL Server é a seguinte:

CREATE PROCEDURE nome_procedimento
(@Parâmetro Tipo_dados)
AS
Bloco de códigos

Exemplos

Vejamos exemplos de criação de uma stored procedure simples.

Exemplo 01

CREATE PROCEDURE sp_teste
AS
SELECT 'Bóson Treinamentos' AS Nome;

Como executar um procedimento armazenado

Para executar um Procedimento Armazenado, use a palavra-chave EXECUTE ou simplesmente EXEC, seguido do nome da procedure.

Sintaxe:

EXEC | EXECUTE nome_procedimento parâmetros

Exemplo:

EXEC sp_teste;

Obs. Se o procedimento armazenado for o primeiro comando de um batch, não é necessário usar a palavra EXEC.

Exemplo 02:

CREATE PROCEDURE sp_LivroValor
AS
SELECT Nome_Livro, Preco_livro
FROM tbl_livros;

Testando:

EXEC sp_LivroValor;

Declaração SET NO COUNT

A declaração SET NO COUNT impede que a mensagem que mostra a contagem do número de linhas afetadas por uma instrução Transact-SQL ou por um procedimento armazenado seja retornada como parte do conjunto de resultados.

  • Valor ON: a conta não é retornada.
  • Valor OFF: a conta é retornada.

Exemplo 03

Digite o código a seguir para criar uma stored procedure de nome sp_teste_autor, que irá retornar os nomes dos livros e seus respectivos autores das tabelas tbl_Livros e tbl_Autores de acordo com o autor fornecido.

CREATE PROCEDURE sp_teste_autor
    @NomeAutor varchar(50)
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT DISTINCT tbl_livros.Nome_Livro, tbl_autores.Nome_Autor FROM tbl_livros
    INNER JOIN tbl_autores
    ON tbl_livros.ID_Autor = tbl_autores.ID_Autor
    WHERE Nome_Autor = @NomeAutor;
END;

Para rodar a procedure:

EXEC sp_livro_autor

Como renomear um procedimento armazenado

É possível renomear um procedimento armazenado usando o procedimento armazenado interno sp_rename:

Sintaxe:

sp_rename 'NOME_ATUAL', 'NOVO_NOME';

Ex.:
Vamos renomear o procedimento armazenado sp_teste_autor para sp_livro_autor, que é um nome mais significativo:

sp_rename 'sp_teste_autor', 'sp_livro_autor';

Visualizar o conteúdo de uma stored procedure

Use o procedimento armazenado sp_helptext para extrair o conteúdo de texto de uma stored procedure, conforme a sintaxe a seguir:

EXEC sp_helptext nome_procedimento

Exemplos:

EXEC sp_helptext sp_LivroValor;
EXEC sp_helptext sp_helptext;

Criptografar o Procedimento Armazenado

É possível criptografar um procedimento armazenado, de modo que seu conteúdo não seja visível com o uso de sp_helptext. Para isso, usamos a declaração WITH ENCRYPTION ao criar ou alterar a procedure, como mostra o exemplo a seguir:

CREATE PROCEDURE sp_LivroISBN
WITH ENCRYPTION
AS
SELECT Nome_Livro, ISBN
FROM tbl_Livro;

Agora tente visualizar seu conteúdo com sp_helptext:

EXEC sp_helptext sp_LivroISBN;

O conteúdo não é mais visível, pois o procedimento armazenado está agora criptografado. Mas, evidentemente, ele continua funcionando normalmente quando executado.

Como Modificar uma Stored Procedure

Podemos modificar um procedimento armazenado usando o comando ALTER PROCEDURE

Sintaxe:

ALTER PROCEDURE nome_procedimento
novo bloco de código da sp

Como excluir um procedimento armazenado

Para excluir um procedimento armazenado usamos o comando DROP PROCEDURE, seguido do nome do procedimento armazenado a excluir:

DROP PROCEDURE Nome_do_SP;

Exercício:

Crie uma stored procedure que permita excluir um livro cadastrado por meio do código do livro.

Solução sugerida:

CREATE PROCEDURE sp_exclui_livros
    @IdLivro SMALLINT
AS 
BEGIN
    SET NOCOUNT ON;
    DELETE FROM tbl_livros 
    WHERE ID_Livro = @IdLivro;
END;

-- Testando: vamos excluir o livro de ID 105. Primeiro vamos ver se o livro existe:
SELECT * from tbl_livros;

-- Agora vamos executar a stored procedure e exclui-lo:
EXEC sp_exclui_livros 105;

-- E verificar se o livro foi realmente excluido:
SELECT * from tbl_livros;

Próximo: Como usar Parâmetros de Entrada em Procedimentos Armazenados

 

Sobre Fábio dos Reis (1356 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 25 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Unix, Linux e Open Source em geral, adora Eletrônica e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.
Contato: Website

Escreva um comentário

Seu e-mail não será divulgado


*