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

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