none
Stored procedure inserting in multiple tables orphans records. How to handle

    Question

  • I have stored procedure that inserts information into a login table and a announcer table. I have realized that when insertion into the announcer fails, a record was inserted into the login table, leaving orphans records into the login table.

        create procedure [publico].[cadastrar_login_Loja] @email char(40), @senha char(10),   @CNPJ char(10),  @Endereco char(40),  @bairro char(40),  @Estado tinyint, @cidade tinyint,  @cep char(20) ,  @RazaoSocial char(50), @NomeFantasia char(50), @Telefone1
    char(15), @Telefone2 char(15), @celular1 char(15), @Celular2 char(15), @Website char(50), @dir char(4)

          as    

          begin   
        
          
          insert into producao.LoginsLojas ( NomeUsuario,Senha) values (@email, @senha);



          insert into producao.Lojas(  cnpj, endereco, bairro, estado, Cidade,  cep, razaosocial, nomefantasia, telefone1, telefone2, celular1, celular2, website, email, dir)

          values (  @cnpj, @endereco, @bairro, @estado, @cidade,  @cep, @razaosocial, @nomefantasia, @telefone,@telefone2, @celular1, @celular2, @website,@email, @dir)
          end

    as you can see, the sp just perform two inserts. How do I handle that? I have studied this scenario before, but I can't remember!

    Thanks!       

    Thursday, May 01, 2014 9:13 PM

Answers

  • As Patrick says, you need a transaction. You also need to have TRY-CATCH to catcn any errors:

    BEGIN TRY
       BEGIN TRANSACTION

        
          insert into producao.LoginsLojas ( NomeUsuario,Senha) values (@email, @senha);

          insert into producao.Lojas(  cnpj, endereco, bairro, estado, Cidade,  cep, razaosocial, nomefantasia, telefone1, telefone2, celular1, celular2, website, email, dir)
          values (  @cnpj, @endereco, @bairro, @estado, @cidade,  @cep, @razaosocial, @nomefantasia, @telefone,@telefone2, @celular1, @celular2, @website,@email, @dir)
          end
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       IF @@trancount > 0 ROLLBACK TRANSACTION
       EXEC error_handler_sp
    END CATCH

    error_handler_sp is a general stored procedure that re-raises the error. You can find it here:
    http://www.sommarskog.se/error_handling_2005.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 01, 2014 10:18 PM

All replies

  • Put your inserts in a transaction. After you have inserted, check the validity, if it's no good, rollback.

    begin transaction
    
    insert into producao.LoginsLojas ( NomeUsuario,Senha) values (@email, @senha);
    
    
    
           insert into producao.Lojas(  cnpj, endereco, bairro, estado, Cidade,  cep, razaosocial, nomefantasia, telefone1, telefone2, celular1, celular2, website, email, dir) 
    
           values (  @cnpj, @endereco, @bairro, @estado, @cidade,  @cep, @razaosocial, @nomefantasia, @telefone,@telefone2, @celular1, @celular2, @website,@email, @dir) 
           end
    
    if exists(select * from producao.lojas l inner join producao.loginslojas ll on l.email = ll.nomeusuario)
    begin
    commit transaction
    end
    
    if not exists(select * from producao.lojas l inner join producao.loginslojas ll on l.email = ll.nomeusuario)
    begin
    rollback transaction
    end

    You may need to debug this a little my Spanish spelling is awful :).

    Thursday, May 01, 2014 9:20 PM
  • How our the tables related? You can perform a single INSERT with OUTPUT clause inserting into both tables at once.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, May 01, 2014 9:50 PM
    Moderator
  • As Patrick says, you need a transaction. You also need to have TRY-CATCH to catcn any errors:

    BEGIN TRY
       BEGIN TRANSACTION

        
          insert into producao.LoginsLojas ( NomeUsuario,Senha) values (@email, @senha);

          insert into producao.Lojas(  cnpj, endereco, bairro, estado, Cidade,  cep, razaosocial, nomefantasia, telefone1, telefone2, celular1, celular2, website, email, dir)
          values (  @cnpj, @endereco, @bairro, @estado, @cidade,  @cep, @razaosocial, @nomefantasia, @telefone,@telefone2, @celular1, @celular2, @website,@email, @dir)
          end
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       IF @@trancount > 0 ROLLBACK TRANSACTION
       EXEC error_handler_sp
    END CATCH

    error_handler_sp is a general stored procedure that re-raises the error. You can find it here:
    http://www.sommarskog.se/error_handling_2005.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 01, 2014 10:18 PM
  • Hello Diego,

    Using TRANSACTION, you can solve your requirement

    Addition to this, you can also think of using SQL OUTPUT clause which enables developers to insert rows into two tables at the same time

    You can place the below INSERT statement into your SQL procedure replacing all other codes

    	insert into LoginsLojas ( NomeUsuario,Senha) 
    		output @cnpj, @endereco,.........
    		into Lojas(cnpj, endereco,..........)
    	values (@email, @senha)

    If the first insert fails, for example a SQL unique constraint, the second insert will not be executed


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Friday, May 02, 2014 6:06 AM
    Moderator
  • Nice use of try/catch.
    Friday, May 02, 2014 7:08 PM