April 27, 2012 at 7:28 am
Hi,
I am creating a stored procedure.
This procedure should only do somethings if others went well.
For example:
1) insert into table ........
2) If the insert went well, then Delete from other table
3) If 2 went well delete from table (other table)
4) report status (1 if procedure went well, 0 if it returns erros).
How can I do this?
Thank you very much
April 27, 2012 at 7:53 am
Here is my suggestion since you really haven't given us much to work with here.
Open Books Online and read about error handling. You have two choices and you need to learn about them. Choice one is the simplistic appoarch and uses the @@ERROR system variable to help determine if an operation is successful. The other option is to wrap your insert and deletes inside TRY CATCH blocks.
April 27, 2012 at 7:57 am
What do you think it's better for this case :
create procedure Facturacao (@maquina varchar(50), @data smalldatetime, @numero varchar(50), @supplier integer, @EstadoFac integer)
as
insert into facturas
select b.productid, a.quantidade, a.desconto, a.iva, a.total, @data, @supplier, @numero, a.preco, @estadofac from temp_facturas1 a, objectos b
where a.maquina = maquina and b.serialnumber = a.referencia
delete from temp_facturas1 where maquina = maquina
delete fromt table 2
return result
?
April 27, 2012 at 8:06 am
Use TRY ... CATCH
If you want everything undone when the error happens do everything within one TRANSACTION
April 27, 2012 at 8:16 am
Thank you.
Let me just put two more question please.
1) I made it like bellow. What do I put in the catch so that It returns 1 if sucess and returns 0 if something happens?
2) I would like to add a third statement (bellow delete) that is a procedure named ActualizaStock
Can I call it inside the try too? if the procedure fails, will my sp go to the catch?
Begin try
insert into facturas
select b.productid, a.quantidade, a.desconto, a.iva, a.total, @data, @supplier, @numero, a.preco, @estadofac from temp_facturas1 a, objectos b
where a.maquina = maquina and b.serialnumber = a.referencia
delete from temp_facturas1 where maquina = maquina
End try
Begin catch
End catch
April 27, 2012 at 8:19 am
Maybe like this:
Begin try
insert into facturas
select b.productid, a.quantidade, a.desconto, a.iva, a.total, @data, @supplier, @numero, a.preco, @estadofac from temp_facturas1 a, objectos b
where a.maquina = maquina and b.serialnumber = a.referencia
delete from temp_facturas1 where maquina = maquina
End try
Begin catch
return 0
End catch
return 1
What do you think? It will return 0 if it goes to the catch instruction (it only goes inside catch instruction if one of the dml raises error) and return 1 if it does not go to the catch (which means that all the instructions completed without errors), correct?
April 27, 2012 at 8:23 am
I tryed to add the procedure like :
Begin try
declare @test-2 as integer
insert into facturas
select b.productid, a.quantidade, a.desconto, a.iva, a.total, @data, @supplier, @numero, a.preco, @estadofac from temp_facturas1 a, objectos b
where a.maquina = maquina and b.serialnumber = a.referencia
delete from temp_facturas1 where maquina = maquina
@test-2 = (exec procedure ActualizaStock)
End try
Begin catch
return 0
End catch
return 1
But it seems like I have an error. It seems like I can not execute the procedure ActualizaStocks this way...
April 27, 2012 at 8:25 am
exec @test-2 = ActualizaStock
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply