Hi!

I'm learning SQL Server, and it is really amazing... but, sometimes, I stay stoped and thinking how to do something simple... Take a look:

I have two stored procedures. The first, select one (or many) records and send it to the second stored procedure, that add a new record into a table.

Ok, said that, I will put my code here:

CREATE Procedure FinalizarProducao
 
@Tab11a int, @Tab11b int
 
As
 
Declare @Qtdade Numeric (9,3)
If @Tab11b > 0
 Begin
  Select @Qtdade = A.QtdPro - COALESCE((Select Sum(Qtdade*Indice) From Tab11c B Where B.Tab11b = A.Tab11b),0)
   From Tab11b A Where A.Tab11b = @tab11b and A.QtdPro > 0
  If @Qtdade > 0
   Exec FinalizarProducao_Item @tab11b, @Qtdade
 End
Else
 Begin
  Declare Tab11b Cursor For
   Select A.Tab11b, A.QtdPro - COALESCE((Select Sum(Qtdade*Indice) From Tab11c B Where B.Tab11b = A.Tab11b),0) As Qtdade
    From Tab11b A Where A.Tab11a = @tab11a and A.QtdPro > 0
  Open Tab11b
  Fetch From Tab11b Into @Tab11b, @Qtdade
  While (@@FETCH_STATUS = 0)
   Begin
    If @Qtdade > 0
     Exec FinalizarProducao_Item @tab11b, @Qtdade
    Fetch Next From Tab11b Into @Tab11b, @Qtdade
   End
  Close Tab11b
  Deallocate Tab11b
 End

CREATE Procedure FinalizarProducao_Item
 
@Tab11b int,
@Qtdade Numeric (9,3)
 
As
 
Declare @Tab11c int
Exec GerarId 'Tab11c', 1, @Tab11c output
 
Insert Into Tab11c (Tab11c, Tab11b, Data, Qtdade, Indice) Values
 (@Tab11c, @Tab11b, GetDate(), @Qtdade, +1)
If @@Error = 0
 Return

So, using VFP, I call this stored procedure like that:

SqlExec(1,'EXEC FinalizarProducao 1350, 0')

And this case, the first store procedure will select all itens from 'Producao = 1350' and send each one to the second store procedure, that will add a new record for them.

If one of this itens can't be added, I have to rollback all itens, so, I need to add a transaction in the first Stored procedure... But I don't know how to test if the second SP works or doesn't work!

In addition, I would like to know if my SQL code is ok, or if I'm doing something wrong.

Thanks in advanced!