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

Fábio dos Reis

12 anos atrás

Criar, editar e excluir procedimento armazenado no SQL Server

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