Peter Vilim wrote :
>I'm working on transfering sps from MS SQL to SAP DB. Is there a way to do
something like this exapmple in SAP DB?
>CREATE PROCEDURE sp_orders_delete
>@ORDER_ID NUMERIC
>AS
>SET NOCOUNT ON
>DECLARE @del_product int,
>@del_orders int,
>-- Start a transaction.
>BEGIN TRAN
>-- Execute the DELETE statement for PRODUCT.
>DELETE product
>WHERE ORDER_ID = @ORDER_ID
>-- Set a variable to the error value for
>-- the DELETE statement.
>SELECT @del_product = @@ERROR
>-- Execute the DELETE statement for ORDERS.
>DELETE orders
>WHERE ORDER_ID = @ORDER_ID
>-- Set a variable to the error value for
>-- the DELETE statement.
>SELECT @del_orders = @@ERROR
>-- Test the error values.
>IF @del_product = 0 AND @del_orders = 0
>BEGIN
>-- Success. Commit the transaction.
>COMMIT TRAN
>END
>ELSE
>BEGIN
>-- An error occurred, roll back the transaction.
>ROLLBACK TRAN
>END
>GO
a similar SAPDB db-procedure could look as follows :
create dbproc sp_orders_delete (IN paramOrder_id integer) AS
subtrans begin;
delete tabowner.product where order_id = :paramOrder_id;
if $rc = 0
then
delete tabowner.orders where order_id = :paramOrder_id;
if $rc <> 0
then
begin
subtrans rollback;
stop ($rc, 'sp_orders_delete failed');
end
else
subtrans end;
Please note, that SAPDB does not allow a transaction rollback from a
db-procedure.
However, using a subtransaction you can make your db-procedure atomic.
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
