--Create tables
create table tbA (invoiceid int identity(1,1) primary key, InvoiceDate datetime)
go
create table tbB (invoiceid int references tbA (invoiceid), lineitemid int identity(1,1) primary key, productid int)
go

--------------------------------------------------------------------
--create called SP
create proc tbB_ins
@invoiceid int
,@productid int
as
set nocount on
insert tbB (invoiceid, productid)
values (@invoiceid, @productid)

if @@error = 0
	return 0

return -1

go

--------------------------------------------------------------------
--create main, transaction controling SP
create proc tbA_ins 
@productid int
as
set nocount on
declare @invid		int
	,@returnCode	int
begin tran

	insert tbA (InvoiceDate)
	values (getdate())
	select @invid = scope_identity()

	if @invid > 2
		goto onError	

	if @invid is not null
	begin
		exec @returnCode = tbB_ins @invoiceid = @invid, @productid = @productid
	end

	if @returnCode = 0
	begin
		commit transaction
		return 0
	end


OnError:
	
if @@trancount > 0
	rollback transaction

raiserror('transaction rolled back', 10,1)
return -1

go
--------------------------------------------------------------------

declare @rc int

--first 2 calls will succeed
exec @rc = tbA_ins @productid = 10
if @rc = 0 print 'transaction completed'

exec @rc = tbA_ins @productid = 11
if @rc = 0 print 'transaction completed'

----third call will fail and transaction will rollback
exec @rc = tbA_ins @productid = 12
if @rc = 0 print 'transaction completed'

--select * from tbA
--select * from tbB
--------------------------------------------------------------------
--clean up

drop proc tbA_ins
drop proc tbB_ins
go
drop table tbB
go
drop table tbA