delete_order_sp

-- PROCEDURE: public.delete_order_sp(integer, integer, character varying, character varying)

-- DROP PROCEDURE public.delete_order_sp(integer, integer, character varying, character varying);

CREATE OR REPLACE PROCEDURE public.delete_order_sp(
    p_mode integer,
    p_id integer,
    p_login character varying,
    INOUT p_output character varying)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    DECLARE
        l_OrderID           INTEGER;
        l_Status            INTEGER;
        l_Name             varchar(50);
        l_now              TIMESTAMP  WITHOUT TIME ZONE;
begin
   l_now = now;

    declare
        exists bit = null;
BEGIN
    select exists=1, l_Status=Status from public.Orders_tb where TID=p_ID;

    p_Output = '';

    if xists = 1 then

        if l_Status = 9 then
            DELETE FROM public.Params_tb WHERE OrderID=p_ID;
            DELETE FROM public.Items_tb WHERE OrderID=p_ID;
            DELETE FROM public.PaymentChanges_t WHERE OrderID=p_ID;
            DELETE FROM public.Payments_tb WHERE OrderID=p_ID;
            DELETE FROM public.Comments_tb WHERE OrderID=p_ID;
            DELETE FROM public.Reviewers_tb WHERE OrderID=p_ID;
            DELETE FROM public.Reviews_tb WHERE OrderID=p_ID;
            DELETE FROM public.OrderDates_tb WHERE OrderID=p_ID;
            DELETE FROM public.OrderDocuments_tb WHERE OrderID=p_ID;
            DELETE FROM public.OrderChanges_tb WHERE OrderID=p_ID;
            DELETE FROM public.Unreads_tb WHERE OrderID=p_ID;
            DELETE FROM public.Orders_tb WHERE TID=p_ID;
        else
            UPDATE public.Orders_tb SET Status=9 WHERE TID=p_ID;
            l_Name = 'Êîðçèíà';
            --
            -- Add to Order Changes log
            --
            INSERT INTO public.OrderChanges_tb(OrderID, Login, Name, Value) VALUES
                (p_ID, p_Login, l_Name, '');
        end if;

        select l_OrderID = p_ID;
        set p_Output = 'Removed';
    else
        l_OrderID = 0;
        p_Output = 'Invalid'
            || ':' || cast(exists as varchar);
    end if;

    p_Output = p_Output || ':' || cast(||l_OrderID as varchar);

    if p_Mode = 0 then
        SELECT l_OrderID, p_Output FROM
            public.sysobjects where id = object_id(N'public.DELETE_Order_sp') and OBJECTPROPERTY(id, N'IsProcedure') = 1;
    else
        return;
        end if;
        end;
    END;
END;
$BODY$;