Como criar uma Stored Procedure Select, insert, delete e/ou update. SQL SERVER

(Creating a Stored Procedure Select, insert, delete and update. SQL SERVER)

Olá, webianos!

Hoje vou explicar como criar uma Stored Procedure em SQL SERVER. Sempre de forma resumida e sintetica.

Mas pq usar uma stored procedure?

1. Desempenho. Mais rapido que command sql, pois o procedimento de caminho ficar registrado no BD (Banco de dados). Assim quando vc  executar a stored procedure o BD não vai precisar mapear o caminho.
2. Manutenção. Imagine que vc tendo uma SP (stored procedure) que retorne só o campo nome de uma lista de produtos. Ai vc quer colocar o codigo do produto junto com o nome. Se usar uma procedure vc concatena o codigo do produto no nome dele, e pronto, onde exibia o nome agora exibirá codigo + nome.
3. Organização. Se utilizar só SP no seu projeto, os procedimento de dados ficará centralizado no BD e de aplicação na aplicação.
4. Tratamento de dados. Vc poderá fazer o tratamento de dados na camada de BD. Assim sua aplicação fica mais limpa, só chamando o SP. O procedimento de analise, work-flow é feito no próprio BD.
5. Camada BD. Para fazer manutenções não será necessario mexer na aplicação. Só alterar a SP e ja está tudo resolvido.

Existe varias razões para usar SP. É uma prática ja utilizada em grandes softwares.

Chega de blablabla, vamos ao que interessa, como criar uma Stored Procedure.

Vc pode utilizar um SP para fazer um procedimento de Select, Insert, Delete e/ou Update. Isso mesmo, vc pode ter um select e um insert na mesma SP. Vc pode usar “if” e regras de negocio dentro de uma SP.

1. Exemplo Select Simples:
CREATE PROCEDURE minhaSP
AS
BEGIN
SELECT * FROM minhaTabela
END

Observe que “minhaSP” eh o nome que vc cria para a procedure, pode ser qualquer nome, o importante é usar um nome semantico. Por exemplo, uma procedure que faz o login, use o nome “sp_login”, ou “sp_faz_login”.

2. Exemplo Select com parametros:
CREATE PROCEDURE minhaSP
@id int
AS
BEGIN
SELECT * FROM minhaTabela WHERE id=@id
END

3. Exemplo Select multiplos parametros:
CREATE PROCEDURE minhaSP
@cor varchar(100),
@placa varchar(100)
AS
BEGIN
SELECT * FROM Carro WHERE cor=@cor and placa=@placa
END

4. Exemplo Insert multiplos parametros:
CREATE PROCEDURE minhaSP
@cor varchar(100),
@placa varchar(100),
@marca varchar(200)
AS
BEGIN
INSERT INTO carro(marca, placa, cor)
VALUES(@marca,@placa,@cor)
END

5. Exemplo update multiplos parametros:
CREATE PROCEDURE minhaSP
@cor varchar(100),
@placa varchar(100),
@marca varchar(200)
AS
BEGIN
UPDATE carro SET cor=@cor, marca=@marca WHERE placa=@placa
END

6. Exemplo Select e Insert:
CREATE PROCEDURE minhaSP
@placa varchar(100)
AS
BEGIN
SELECT * FROM carro WHERE placa=@placa

INSERT INTO log_placa_pesquisada(placa)
VALUES(@placa)
END

7. Exemplo Select integrado Insert:
CREATE PROCEDURE minhaSP
@id int
AS
BEGIN
declare @placa varchar(100)
SELECT @placa=placa FROM carro WHERE id=@id

INSERT INTO log_placa_pesquisada(placa)
VALUES(@placa)
END

8. Exemplo Delete com parametros:
CREATE PROCEDURE minhaSP
@id int
AS
BEGIN
DELETE minhaTabela WHERE id=@id
END

9. Exemplo Delete com Insert:
CREATE PROCEDURE minhaSP
@id int
AS
BEGIN
DELETE minhaTabela WHERE id=@id

INSERT INTO log_delete(id)
VALUES(@id)
END

10. Exemplo Select Dinâmico: (colaboração de Sandro Lima)
CREATE PROCEDURE [dbo].[minhaSP]
@nome varchar(50),
@dataNascimento datetime,
@email varchar(100)
AS
DECLARE @sql varchar(900)
SET @SQL = ‘select * from tblUsuario where 1=1′
IF (@nome IS NOT NULL)
BEGIN
SET @SQL = @SQL + ‘ AND Nome = ‘+ QUOTENAME(@nome,””)
END

IF (@dataNascimento IS NOT NULL)
BEGIN
set @SQL = @SQL + ‘ AND dataNascimento = ‘ + QUOTENAME(@dataNascimento,””)
END

IF (@email IS NOT NULL)
BEGIN
set @SQL = @SQL + ‘ AND email = ‘ + QUOTENAME(@email,””)
END

exec(@SQL)

Exemplos de uso:
exec minhaSP sandro,null,null
exec minhaSP null,’2008-02-04′,null
exec minhaSP null,null,sandro.lima@asoec.com.br

Bom uso, e se tiver alguma dúvida deixe seu comentario com email para eu responder.

About these ads

20 Respostas to “Como criar uma Stored Procedure Select, insert, delete e/ou update. SQL SERVER”

  1. Flavio Says:

    Mestre…

    Queria saber se vc etm outros artigos sobre store procedure..

    Flavio

  2. EDMILSON DIAS SANTOS Says:

    TENTO EXECUTAR A ULTIMA PROCEDURE E RETORNA ESTAS MENSAGENS DE ERRO.

    Msg 156, Level 15, State 1, Procedure minhaSP, Line 7
    Incorrect syntax near the keyword ‘select’.
    Msg 137, Level 15, State 2, Procedure minhaSP, Line 10
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Procedure minhaSP, Line 15
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Procedure minhaSP, Line 20
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Procedure minhaSP, Line 23
    Must declare the scalar variable “@SQL”.

  3. HANIEL Says:

    OLA!!
    TENHO QUE FAZER UM SISTEMA DE LOCADORA, MAIS NÃO CONSIGO CRIAR UMA PROCEDURE DE INSERT, UPDATE E DELETE PARA A TABELA CLIENTE, PEÇO A AJUDA DE VCS.
    SOU GRATO PELA ATENÇÃO

  4. Endrigo Says:

    Olá, tem alguma maneira de fazer uma store procedure que me exiba todos os inserts que foram inseridos( das tabelas ) … em forma de comando, como se eu estivesse inserindo, só que eles já foram inseridos … Dessa maneira eu não precisaria ficar inserindo dados no banco toda vez que chega um versão do sistema pra teste, ai só executaria os inserts, seria uma mão na roda, abraço

  5. ALTEMIR Says:

    COMO POSSO CRIAR UMA SP ONDE DENTRO DO SELECT TENHO UMA SUBQUERY COM TABELA DINAMICA

    PRECISO PEGAR O CAMPO ID DO SELECT PARA MONTAR O NOME DA TABELA DO SUBSELECT

    ALGUEM JA FEZ ISSO?

    CREATE PROCEDURE SP_CTAS_APAGAR_CONTABIL
    (
    @ID_EMPRESA VARCHAR(3),
    @ANO_EXERCICIO VARCHAR(4)
    )
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @_SLD VARCHAR(3)
    DECLARE @_ID VARCHAR(3)
    DECLARE @TBLSALDO VARCHAR(10)
    DECLARE @SQL_SP VARCHAR(1500)
    SET @_SLD = ”’SLD”’
    SET @_ID = ‘+”ID”+’
    SET @TBLSALDO = @_SLD+@_ID+@ANO_EXERCICIO
    SET @SQL_SP = ‘
    SELECT ID,
    (SELECT SUM(INICIAL+DMC01-CMC01+DMC02-CMC02+DMC03-CMC03+DMC04-CMC04+DMC05-CMC05+DMC06-CMC06+DMC07-CMC07+DMC08-CMC08+DMC09-CMC09+DMC10-CMC10+DMC11-CMC11+DMC12-CMC12)
    FROM ”’+LTRIM(@TBLSALDO)+”’
    WHERE LEFT(CODIGO,5)=”11111” AND LEN(CODIGO)>5) AS SALDO_CAIXA,

    (SELECT SUM(INICIAL+DMC01-CMC01+DMC02-CMC02+DMC03-CMC03+DMC04-CMC04+DMC05-CMC05+DMC06-CMC06+DMC07-CMC07+DMC08-CMC08+DMC09-CMC09+DMC10-CMC10+DMC11-CMC11+DMC12-CMC12)
    FROM ”’+LTRIM(@TBLSALDO)+”’
    WHERE LEFT(CODIGO,5)=”11121” AND LEN(CODIGO)>5) AS SALDO_BANCO ,

    (SELECT SUM(INICIAL+DMC01-CMC01+DMC02-CMC02+DMC03-CMC03+DMC04-CMC04+DMC05-CMC05+DMC06-CMC06+DMC07-CMC07+DMC08-CMC08+DMC09-CMC09+DMC10-CMC10+DMC11-CMC11+DMC12-CMC12)
    FROM ”’+LTRIM(@TBLSALDO)+”’
    WHERE LEFT(CODIGO,5)=”11122” AND LEN(CODIGO)>5) AS SALDO_APLICACAO
    FROM CLIENTE
    WHERE ID IN (SELECT EMPRESA FROM CLIENTE_GRUPO WHERE GRUPO=”’+@ID_EMPRESA+”’)’
    EXECUTE(@SQL_SP)
    END

  6. Lu Says:

    Será que vc poderia me ajudar? não sei se é exatamente isso que estou procurando, mas penso que vc possa me esclarecer..
    Bem, eu estou trabalhando em uma firma aqui na suíça e começando minha vida “profissional”, pois ainda não terminei os estudos de informática e por isso estou como estágiaria aqui nessa firma.
    Meu conhecimento na área de sql é muito pequeno, sei só o básico dos básicos, e meu chefe pediu pra que eu fizesse algumas mudanças em algumas informações para o cliente, como por exemplo o nr. do produto/artigo para outro.
    Coisa bem normal, mas como foram várias tabelas que tinham esse produto/artigo, tive que mudar em todas elas.
    Então me deram a sugestão de eu preparar um ‘script’, que eu entendi como procedure, para que eu não precisasse escrever tantas vezes os updates só pra uma tabela.. tudo bem que dessa vez eram só 4 produtos/artigos, mas eram 10 tabelas, ou seja, 40 vezes o update :/ se fossem mais, ficaria um bom tempo fazendo isso.
    Por isso ficaria muito grata se vc me ajudasse, pq não tô conseguindo não.
    Fico aguardando sua ajuda.

  7. Danilo Says:

    Eu posso usar um parametro para definir um campo na busca? Se sim, como eu faço?
    por exemplo:

    @Campo varchar(50)
    @Texto varchar(50)

    select * from TABELA where @campo = @texto

    • webmaicon Says:

      Sim, é possivel. Para isso vc terá que usar o EXEC.
      Veja no exemplo “10. Exemplo Select Dinâmico: (colaboração de Sandro Lima)”

      Abaixo um exemplo q fiz sem testar. Teste, corrija e nos envie.
      CREATE PROCEDURE [dbo].[minhaSP]
      @Campo varchar(50),
      @Texto varchar(50)
      AS
      DECLARE @sql varchar(900)
      SET @SQL = ‘select * from TABELA where ‘ + @Campo + ‘ = ‘ + @Texto
      END

      exec(@SQL)

      • Danilo Says:

        Não tinha pensado nesse modo, desculpe.
        Muito obrigado pela ajuda.

        Não consegui testar ainda pois não estou com meu note, mas no “olhometro” vai funcionar, só esta faltando uma aspa simples no final do @Texto.

        Chegando em casa vou testar e se funcionar eu aviso.
        Muito obrigado pelo post e por responder, primeiro blog que o autor responde e é educado.

      • Danilo Says:

        Não esta faltando aspa simples, olhei correndo, desculpe.

  8. Ismael Says:

    Estou com a seguinte questão:

    Em um banco de dados de uma loja virtual existe uma tabela PRODUTOS.
    Toda vez que um determinado produto estiver com menos de 5 unidades em estoque,
    é desejado que seja inserido na tabela COMPRAS_REPOSICAO um registro com a descrição
    do produto para que o dono da loja possa providenciar o aumento do estoque.

    A partir dai eu criei a tabela Produtos

    create table produtos
    (
    id int not null unsigner auto_increment,
    descricao varchar (50),
    quantidade int,
    primary key (id)
    );

    create table compras_reposicao
    (
    id int not null auto_increment,
    produto varchar(50) ,
    primary key(id)

    );

    Criei a Stored Procedure para fazer o que o enunciado pede:

    CREATE PROCEDURE `PRODUTO_FALTANDO`()
    BEGIN

    DECLARE DES VARCHAR(50);

    SELECT @DES=DESCRICAO FROM PRODUTOS WHERE QUANTIDADE<5;

    INSERT INTO COMPRAS_REPOSICAO (PRODUTO) VALUES(@DES);

    END

    O código insere um nomo registro na tablea mas no campo produto o valor inserido é null. Só fica o ID que é auto incrementado.

    • webmaicon Says:

      Ola, ismael.

      Veja as seguintes dicas:

      1. use identificador unico por tabela, ou seja substituia “id” por “id_tabela”. exemplo: id_produtos, id_compras_reposicao
      2. na tabela produtos acrescente o campo “quantidade_tipo”, pra representar a “unidade” de medida da quantidade, existe produtos que são “unidades”, “caixas”, “pacotes”.. etc.
      3. Altere a tabela “compras_reposicao”, em vez de inserir o “produto”, insira o “id_produtos”. Assim se houver alteração no nome do produto não ficará com dois nomes em duas tabelas. Além disso ajudará na criação de relatorios.


Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 278 outros seguidores

%d blogueiros gostam disto: