Stored Procedures em SQL Server 2008

Alguns exemplos de stored procedure que utilizei, a nível de estudo, em banco de dados SQL Server 2008 que podem servir de guia para outras ocasiões.

Ex1:

1. CREATE PROCEDURE SP_VALIDA_MATRICULA
3.         @CODIGO NCHAR(20),
4.         @NOME nvarchar(50) --no primeiro exemplo não é usado
5. AS     
6. BEGIN
7.         IF EXISTS (SELECT 1
8.                 FROM FUNCIONARIO
9.                 WHERE FUNCIONARIO.MATRICULA=@CODIGO )
10.                         SELECT 'Funcionario valido. Operacao realizada!'
11.         ELSE
12.                         SELECT 'Funcionario invalido. Operacao nao realizada!'
13. END;
    
    

Verifica se existe funcionário com um determinado código de matrícula dentro da tabela. A vantagem deste tipo de operação é que a pessoa que realiza o teste não precisa ter conhecimento sobre o banco de dados em si pois receberá somente o nome da procedure. Para executar:

 EXEC SP_VALIDA_MATRICULA 152345,'MARCIO DOS SANTOS'


Ex2:

1. CREATE PROCEDURE SP_VALIDA_MATRICULA
3.         @CODIGO NCHAR(20),
4.         @NOME nvarchar(50)
5. AS     
6. BEGIN
7.         IF EXISTS (SELECT 1
8.                 FROM FUNCIONARIO
9.                 WHERE FUNCIONARIO.MATRICULA=@CODIGO )
10.                         INSERT INTO DEPENDENTE VALUES (@CODIGO,@NOME)
11.         ELSE
12.                         SELECT 'Funcionario invalido. Operacao nao realizada!'
13. END;

Neste exemplo o funcionário, caso não exista, será inserido na tabela.


Supondo agora a tabela de testes abaixo:

1. CREATE TABLE dbmar(
2. NUMBER INT,
3. firstname VARCHAR(20)
4. )

Detalhe que NUMBER é uma palavra reservada e não deveria ser utilizada para nome de campo na tabela, mesmo assim o exemplo funcionou.


Ex1:

1. USE forcecar --- Nome da DataBase
2. GO
3. CREATE PROCEDURE testeBusca --- Declarando o nome da procedure
4. @NUMBER INT=2 --- Declarando varivel (note que utilizamos o @ antes do nome da varivel)
5. AS
6. --SELECT number, firstname --- Consulta
7. --FROM dbmar
8. UPDATE dbmar
9. SET firstname='roiMAS'
10. WHERE NUMBER = @NUMBER --- Utilizando varivel como filtro para a consulta
13. --Para executar uma procedure basta utiilizar a clusula EXECUTE seguido pelo nome da procedure e na frente o valor a ser utilizado como parmetro.
14. --Exemplo:
16. EXECUTE testeBusca '3'
18. --Para excluir a procedure
19. DROP PROCEDURE testeBusca

Outro exemplo:

1. CREATE PROCEDURE sp_vendas
3.         @NumeroNF INT,
4.         @CodProduto INT,
5.         @desconto       NUMERIC(4,2),
6.         @quantidade     NUMERIC(10,2)
8. AS
10.  /* CRIA AS VARIAVEIS LOCAIS */
11. DECLARE @estoque        NUMERIC(10,2)
12. DECLARE @msg            CHAR(100)
14.  /* DEFINE O VALOR DE CADA VARIÁVEL */
15. SET @estoque = (SELECT quantidade FROM PRODUTO WHERE CodProduto = @codproduto)
17.  /* EXECUTO O TESTE, UTILIZANDO IF...ELSE */
19. IF (@estoque) < (@quantidade)
20.         BEGIN
21.                 SET @msg = 'A quantidade a ser vendida é maior que a quantidade em estoque'
22.                 PRINT(@msg)
23.         END
24. ELSE
25.         BEGIN
26.                 INSERT produtoitem (numeronf, codproduto, desconto, quantidade)
27.                 VALUES (@numeronf, @codproduto, @desconto, @quantidade)    
29.                 UPDATE PRODUTO SET Quantidade = (@estoque-@quantidade) WHERE CodProduto=@codproduto
30.         END
    
    

Verifica possibilidade de venda de produto caso exista em estoque.


Para executar:
exec sp_vendas 1,22,10,100

A procedure pode também ser criada com o comando abaixo:


CREATE OR REPLACE PROCEDURE sp_vendas

Pois, caso já exista uma procedure com o nome não é possível criar outra com o mesmo nome e o replace substitui a anterior.