add_reviewer_s

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

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

CREATE OR REPLACE PROCEDURE public.add_reviewer_sp(
    p_mode integer,
    p_orderid integer,
    p_login character varying,
    p_value character varying,
    p_md integer,
    INOUT p_output character varying)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN

    DECLARE
        l_TID          int = null;
        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_OrderID;

    if exists = 1
    then
         p_Value = public.Strip_fn(p_Value);
        --
        -- Check if this is Reviewer line
        --
        set exists = 0;

        if public.CHECK_IsEmpty_fn(p_Value) = 0  then
            select exists=1 from public.Reviewers_tb where OrderID=p_OrderID and Login=p_Value;

            if exists = 0 then
                INSERT INTO public.Reviewers_tb VALUES (
                    p_OrderID,
                    p_Value);
                select l_TID = CAST(scope_identity() AS integer);
                set p_Output = 'Registered';
            end if;
        end if;
        --
        -- Add to Order Changes log
        --
        INSERT INTO public.OrderChanges_tb (OrderID, Login, Name, Value) VALUES
            (p_OrderID, p_Login, 'Назначен рецензент', p_Value);

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

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

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