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

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 :).