ADD_Refer_sp

CREATE OR REPLACE PROCEDURE public.ADD_Refer_sp(
    p_Mode             int,
    p_OrderID          int,
    p_ID               int,
    p_ReferID          int,
    p_Login            varchar(50),
    p_NewRefer         varchar(50),
    p_Value            int,
    p_Note             varchar(1000),
    p_MD               int,
   inout p_Output           varchar(20))
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN


    DECLARE
        l_ReferID      int = null;
        l_TID          int = null;
        l_ReferName    varchar(50);
        l_now          TIMESTAMP  WITHOUT TIME ZONE;

BEGIN
    set l_now = now;

    declare
        exists bit = null;

begin
    select exists=1 from public.Orders_tb where TID=p_Value;

    if exists = 1 then
        --
        -- Check Refers Reference
        --
        if p_ReferID is null or p_ReferID = 0 then
            select l_ReferID=TID from public. DIC_Refers_tb where Name=p_NewRefer and (MD=p_MD or MD is null);
            if l_ReferID is null then
                INSERT INTO public.DIC_Refers_tb(Name, MD) VALUES(p_NewRefer, @p_MD);
                select l_ReferID = CAST(scope_identity() AS int);
            end if;
         else
            set l_ReferID = p_ReferID;
        --
        -- Get Refer name and value
        --
        select l_ReferName=Name from public.DIC_Refers_tb where TID=l_ReferID ;
        --
        -- Check if this is OrderRefer line (like as Params extentions)
        --
        set exists = 0;

        if exists = 0 then
            if p_ID = 0 then
                set l_TID = 0;
            else
                set l_TID = p_ID;
            end if;
        end if;

        if l_TID > 0 then
            UPDATE public.OrderRefers_tb SET
                OrderID=p_OrderID,
                ReferID=l_ReferID,
                OrderReferID=p_Value,
                Login=p_Login,
                Note=p_Note,
                RD=l_now
            WHERE TID=l_TID;

            set p_Output = 'Updated';
         else
            INSERT INTO public.OrderRefers_tb(
                OrderID,
                ReferID,
                OrderReferID,
                Login,
                Note,
                RD
            ) VALUES (
                p_OrderID,
                l_ReferID,
                p_Value,
                p_Login,
                p_Note,
                l_now);

            select l_TID = CAST(scope_identity() AS integer);
            set p_Output = 'Registered';
        end if;
        --
        -- Add to Order Changes log
        --
        INSERT INTO public.OrderChanges_tb(OrderID, Login, Name, Value) VALUES
            (p_OrderID, p_Login, 'Ссылки:' || l_ReferName, p_Value);

        if error != 0  then
            raise'ошибка обработки';
        else
        set l_TID = 0;
        set p_Output = 'Invalid';
        end if;

    p_Output = p_Output || ':' || cast(l_TID as varchar) || ':' || cast(l_ReferID as varchar);

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