none
Store Procedure RRS feed

  • Pergunta

  • Olá, tenho um BD em SQL Server 2008 R2, que contem as seguintes tabelas:

    CLIENTE, ENDERECO, TELEFONE.

    Onde as tabelas endereço, telefone fazem relacionamento com CLIENTE. A dúvida é a seguinte: 

     

    Quero criar uma store procedure onde vou inserir um cliente, mas com os dados de endereço e telefone. Como posso criar esta store procedure? 

    Para uma unica tabela não tenho problemas, o meu problema maior é para mais de uma.

     

    Obrigado


    André Noberto
    segunda-feira, 30 de maio de 2011 14:19

Respostas

  • Bom Dia,

    Acredito que o uso de XML é uma ótima opção para isso. Ex:

    -- Cria as tabelas
    CREATE TABLE Clientes (
     ClienteID INT IDENTITY(1,1),
     Nome VARCHAR(50) NOT NULL)
    CREATE TABLE Telefones (
     TelID INT IDENTITY(1,1),
     ClienteID INT NOT NULL,
     DDD CHAR(3) NOT NULL,
     Numero CHAR(8) NOT NULL)
    CREATE TABLE Enderecos (
     EndID INT IDENTITY(1,1),
     ClienteID INT NOT NULL,
     Logradouro VARCHAR(150) NOT NULL,
     CEP CHAR(10))
    GO
    -- Cria a SP
    CREATE PROCEDURE UspInsereCliente
     @XML XML
    As
    -- Cadastra o cliente
    INSERT INTO Clientes (Nome)
    SELECT @XML.value('(/Cliente/@Nome)[1]','VARCHAR(50)')
    -- Cadastra os telefones
    INSERT INTO Telefones (ClienteID, DDD, Numero)
    SELECT SCOPE_IDENTITY(),
     C.t.value('@DDD','CHAR(3)'),
     C.t.value('@Numero','CHAR(8)')
    FROM @XML.nodes('/Cliente/Telefones/Telefone') C(t)
    -- Cadastra os endereços
    INSERT INTO Enderecos (ClienteID, Logradouro, CEP)
    SELECT SCOPE_IDENTITY(),
     C.e.value('@Logradouro','VARCHAR(150)'),
     C.e.value('@CEP','CHAR(10)')
    FROM @XML.nodes('/Cliente/Enderecos/Endereco') C(e)
    GO
    -- Verifica as tabelas
    SELECT * FROM Clientes
    SELECT * FROM Telefones
    SELECT * FROM Enderecos
    -- Executa a SP
    DECLARE @DadosCliente XML
    SET @DadosCliente = '<Cliente Nome="Raimundo">
    <Telefones>
    <Telefone DDD="061" Numero="81230043"/>
    <Telefone DDD="061" Numero="92173123"/>
    </Telefones>
    <Enderecos>
    <Endereco Logradouro="Rua X Bloco B Apt 401" CEP="70221-321"/>
    </Enderecos>
    </Cliente>'
    EXEC UspInsereCliente @XML = @DadosCliente
    -- Verifica as tabelas
    SELECT * FROM Clientes
    SELECT * FROM Telefones
    SELECT * FROM Enderecos
    

    Maiores detalhes em:

    Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I
    http://gustavomaiaaguiar.wordpress.com/2010/06/02/algumas-implementacoes-multivaloradas-com-xml-e-table-value-parameter-%E2%80%93-parte-i/

    Algumas implementações multivaloradas com XML e Table Value Parameter – Parte II
    http://gustavomaiaaguiar.wordpress.com/2010/06/02/algumas-implementacoes-multivaloradas-com-xml-e-table-value-parameter-%e2%80%93-parte-ii/

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar segunda-feira, 30 de maio de 2011 14:40
    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:42
    segunda-feira, 30 de maio de 2011 14:40
  • Andre,

     

    Veja se o codigo a seguir te ajuda:

     

    --CRIAÇÃO DAS TABELAS

    create table cliente (id int identity(1,1) primary key, nome varchar(100))

    create table endereco (id int identity(1,1) primary key, id_cliente int references cliente(id), endereco varchar(100))

    create table telefone (id int identity(1,1) primary key, id_cliente int references cliente(id), telefone varchar(100))

    --PROCEDURE

    alter procedure cadastra_cliente

    @cliente varchar(100),

    @End1 varchar(100),

    @End2 varchar(100),

    @End3 varchar(100),

    @Tel1 varchar(100),

    @Tel2 varchar(100),

    @Tel3 varchar(100)

    as

    insert into cliente(nome) values (@cliente)

    declare @id_cliente int 

    set @id_cliente = (select id from cliente where nome like @cliente)

    if(@End1 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End1)

    if(@End2 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End2)

    if(@End3 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End3)

    if(@Tel1 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel1)

    if(@Tel2 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel2)

    if(@Tel3 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel3)

    --TESTE

    exec cadastra_cliente @cliente='fabrizzio', @end1='Rua x', @end2=null,@end3=null, @tel1='1111-1111', @tel2='2222-2222', @tel3=null


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:43
    segunda-feira, 30 de maio de 2011 14:45
    Moderador
  • André, pelo que entendi, você quer simplesmente criar uma única procedure na qual passará informações cujo armazenamento deverá ser dividido entre as tabelas Cliente, Endereço e Telefone. É isso mesmo? Se for, não entendi muito bem o problema. Pois da mesma maneira que você criaria a procedure para inserir numa tabela, você cria para inserir nas 3 tabelas.

    Exemplo:

    Create Table TabelaCliente (CodCliente int Primary Key, Nome VarChar(30))

    Create Table TabelaEnderecoCliente (CodCliente int Foreign Key References TabelaCliente(CodCliente), Endereco VarChar(30))

    Create Table TabelaTelefoneCliente (CodCliente int Foreign Key References TabelaCliente(CodCliente), Telefone VarChar(10))

     

    Create Procedure dbo.stp_InsereCliente

      @Codigo   int,

      @Nome     VarChar(30),

      @Endereco VarChar(30),

      @Telefone VarChar(10) as

    Begin

      Set NoCount On

     

      Insert Into TabelaCliente (CodCliente, Nome) Values (@Codigo, @Nome)

      Insert Into TabelaEnderecoCliente (CodCliente, Endereco) Values (@Codigo, @Endereco)

      Insert Into TabelaTelefoneCliente (CodCliente, Telefone) Values (@Codigo, @Telefone)

     

      Set NoCount Off

    End

     

    Exec stp_InsereCliente 1, 'Pedro', 'São Paulo', '2234-5678'

    Exec stp_InsereCliente 2, 'Tiago', 'Rio de Janeiro', '2245-1020'

    Exec stp_InsereCliente 3, 'João' , 'Brasília', '2298-9835'

     

    Select * From TabelaCliente

    Select * From TabelaEnderecoCliente

    Select * From TabelaTelefoneCliente


    Roberson Ferreira - Database Developer


    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:42
    segunda-feira, 30 de maio de 2011 16:01
    Moderador

Todas as Respostas

  • Bom Dia,

    Acredito que o uso de XML é uma ótima opção para isso. Ex:

    -- Cria as tabelas
    CREATE TABLE Clientes (
     ClienteID INT IDENTITY(1,1),
     Nome VARCHAR(50) NOT NULL)
    CREATE TABLE Telefones (
     TelID INT IDENTITY(1,1),
     ClienteID INT NOT NULL,
     DDD CHAR(3) NOT NULL,
     Numero CHAR(8) NOT NULL)
    CREATE TABLE Enderecos (
     EndID INT IDENTITY(1,1),
     ClienteID INT NOT NULL,
     Logradouro VARCHAR(150) NOT NULL,
     CEP CHAR(10))
    GO
    -- Cria a SP
    CREATE PROCEDURE UspInsereCliente
     @XML XML
    As
    -- Cadastra o cliente
    INSERT INTO Clientes (Nome)
    SELECT @XML.value('(/Cliente/@Nome)[1]','VARCHAR(50)')
    -- Cadastra os telefones
    INSERT INTO Telefones (ClienteID, DDD, Numero)
    SELECT SCOPE_IDENTITY(),
     C.t.value('@DDD','CHAR(3)'),
     C.t.value('@Numero','CHAR(8)')
    FROM @XML.nodes('/Cliente/Telefones/Telefone') C(t)
    -- Cadastra os endereços
    INSERT INTO Enderecos (ClienteID, Logradouro, CEP)
    SELECT SCOPE_IDENTITY(),
     C.e.value('@Logradouro','VARCHAR(150)'),
     C.e.value('@CEP','CHAR(10)')
    FROM @XML.nodes('/Cliente/Enderecos/Endereco') C(e)
    GO
    -- Verifica as tabelas
    SELECT * FROM Clientes
    SELECT * FROM Telefones
    SELECT * FROM Enderecos
    -- Executa a SP
    DECLARE @DadosCliente XML
    SET @DadosCliente = '<Cliente Nome="Raimundo">
    <Telefones>
    <Telefone DDD="061" Numero="81230043"/>
    <Telefone DDD="061" Numero="92173123"/>
    </Telefones>
    <Enderecos>
    <Endereco Logradouro="Rua X Bloco B Apt 401" CEP="70221-321"/>
    </Enderecos>
    </Cliente>'
    EXEC UspInsereCliente @XML = @DadosCliente
    -- Verifica as tabelas
    SELECT * FROM Clientes
    SELECT * FROM Telefones
    SELECT * FROM Enderecos
    

    Maiores detalhes em:

    Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I
    http://gustavomaiaaguiar.wordpress.com/2010/06/02/algumas-implementacoes-multivaloradas-com-xml-e-table-value-parameter-%E2%80%93-parte-i/

    Algumas implementações multivaloradas com XML e Table Value Parameter – Parte II
    http://gustavomaiaaguiar.wordpress.com/2010/06/02/algumas-implementacoes-multivaloradas-com-xml-e-table-value-parameter-%e2%80%93-parte-ii/

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar segunda-feira, 30 de maio de 2011 14:40
    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:42
    segunda-feira, 30 de maio de 2011 14:40
  • Andre,

     

    Veja se o codigo a seguir te ajuda:

     

    --CRIAÇÃO DAS TABELAS

    create table cliente (id int identity(1,1) primary key, nome varchar(100))

    create table endereco (id int identity(1,1) primary key, id_cliente int references cliente(id), endereco varchar(100))

    create table telefone (id int identity(1,1) primary key, id_cliente int references cliente(id), telefone varchar(100))

    --PROCEDURE

    alter procedure cadastra_cliente

    @cliente varchar(100),

    @End1 varchar(100),

    @End2 varchar(100),

    @End3 varchar(100),

    @Tel1 varchar(100),

    @Tel2 varchar(100),

    @Tel3 varchar(100)

    as

    insert into cliente(nome) values (@cliente)

    declare @id_cliente int 

    set @id_cliente = (select id from cliente where nome like @cliente)

    if(@End1 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End1)

    if(@End2 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End2)

    if(@End3 is not null)

    insert into endereco(id_cliente, endereco) values (@id_cliente, @End3)

    if(@Tel1 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel1)

    if(@Tel2 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel2)

    if(@Tel3 is not null)

    insert into telefone(id_cliente, telefone) values (@id_cliente, @tel3)

    --TESTE

    exec cadastra_cliente @cliente='fabrizzio', @end1='Rua x', @end2=null,@end3=null, @tel1='1111-1111', @tel2='2222-2222', @tel3=null


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:43
    segunda-feira, 30 de maio de 2011 14:45
    Moderador
  • Obrigado pela resposta Gustavo, farei o teste e posto o resultado.
    André Noberto
    segunda-feira, 30 de maio de 2011 15:18
  • André, pelo que entendi, você quer simplesmente criar uma única procedure na qual passará informações cujo armazenamento deverá ser dividido entre as tabelas Cliente, Endereço e Telefone. É isso mesmo? Se for, não entendi muito bem o problema. Pois da mesma maneira que você criaria a procedure para inserir numa tabela, você cria para inserir nas 3 tabelas.

    Exemplo:

    Create Table TabelaCliente (CodCliente int Primary Key, Nome VarChar(30))

    Create Table TabelaEnderecoCliente (CodCliente int Foreign Key References TabelaCliente(CodCliente), Endereco VarChar(30))

    Create Table TabelaTelefoneCliente (CodCliente int Foreign Key References TabelaCliente(CodCliente), Telefone VarChar(10))

     

    Create Procedure dbo.stp_InsereCliente

      @Codigo   int,

      @Nome     VarChar(30),

      @Endereco VarChar(30),

      @Telefone VarChar(10) as

    Begin

      Set NoCount On

     

      Insert Into TabelaCliente (CodCliente, Nome) Values (@Codigo, @Nome)

      Insert Into TabelaEnderecoCliente (CodCliente, Endereco) Values (@Codigo, @Endereco)

      Insert Into TabelaTelefoneCliente (CodCliente, Telefone) Values (@Codigo, @Telefone)

     

      Set NoCount Off

    End

     

    Exec stp_InsereCliente 1, 'Pedro', 'São Paulo', '2234-5678'

    Exec stp_InsereCliente 2, 'Tiago', 'Rio de Janeiro', '2245-1020'

    Exec stp_InsereCliente 3, 'João' , 'Brasília', '2298-9835'

     

    Select * From TabelaCliente

    Select * From TabelaEnderecoCliente

    Select * From TabelaTelefoneCliente


    Roberson Ferreira - Database Developer


    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Eder Costa quarta-feira, 1 de junho de 2011 17:42
    segunda-feira, 30 de maio de 2011 16:01
    Moderador