Форум программистов [Powered by Invision Power Board]

Код

CREATE PROCEDURE NOTE_LIST (
    @ID_ARM NUMERIC=0,
    @ID_NOTE_TYPE TINYINT=NULL, 
    @DATE_FROM DATETIME=NULL,
    @DATE_TO DATETIME=NULL,
    @OUT NUMERIC=NULL,
    @IN NUMERIC=NULL,
    @ORDER_BY VARCHAR(255)=''
) AS
DECLARE 
    @NAME_IN VARCHAR(255),
    @NAME_OUT VARCHAR(255)

SELECT 
    ID_NOTE, D_DOC, ID_OUT, ID_IN, N_DOC, T1.ID_ARM,
    CASE WHEN @ID_ARM=T1.ID_ARM THEN T1.ACCEPT_MASTER
    WHEN T3.DIVISION=T1.ID_SLAVE THEN T1.ACCEPT_SLAVE 
     ELSE '' END AS ACCEPT,
    CASE 
        WHEN T2.CAN_WRITE=1 AND T2.ID_ARM=T1.ID_ARM THEN 1
        WHEN T2.CAN_WRITE=2 THEN 1
        ELSE 0 
    END AS CAN_WRITE , 
    CASE 
        WHEN T2.CAN_DEL=1 AND T2.ID_ARM=T1.ID_ARM THEN 1
        WHEN T2.CAN_DEL=2 THEN 1
        ELSE 0 
    END AS CAN_DEL ,
    CASE T1.TABLE_OUT
        WHEN 0 THEN (SELECT NAME FROM INSIDE WHERE ID=ID_OUT)
        WHEN 1 THEN (SELECT RTRIM(NAME) FROM LIM_BIN.dbo.UL WHERE ID_UL=ID_OUT)
        WHEN 2 THEN (SELECT NAME FROM ACT WHERE ID_ACT=ID_OUT)
    END AS NAME_OUT,
    CASE T1.TABLE_IN
        WHEN 0 THEN (SELECT NAME FROM INSIDE WHERE ID=ID_IN)
        WHEN 1 THEN (SELECT RTRIM(NAME) FROM LIM_BIN.dbo.UL WHERE ID_UL=ID_IN)
        WHEN 2 THEN (SELECT NAME FROM ACT WHERE ID_ACT=ID_IN)
    END AS NAME_IN,
--ДАЛЕЕ СОРТИРОВКА
    CASE 
    WHEN UPPER(@ORDER_BY) IN ('D', 'D_DOC') THEN CAST(D_DOC AS VARCHAR(255))
    WHEN UPPER(@ORDER_BY) IN ('N', 'N_DOC') THEN STR(N_DOC, 20,0)
    WHEN UPPER(@ORDER_BY) IN ('O', 'NAME_OUT')  THEN 
        CASE T1.TABLE_OUT
            WHEN 0 THEN (SELECT NAME FROM INSIDE WHERE ID=ID_OUT)
            WHEN 1 THEN (SELECT RTRIM(NAME) FROM LIM_BIN.dbo.UL WHERE ID_UL=ID_OUT)
            WHEN 2 THEN (SELECT NAME FROM ACT WHERE ID_ACT=ID_OUT)
        END
    WHEN UPPER(@ORDER_BY) IN ('I', 'NAME_IN') THEN 
        CASE T1.TABLE_IN
            WHEN 0 THEN (SELECT NAME FROM INSIDE WHERE ID=ID_IN)
            WHEN 1 THEN (SELECT RTRIM(NAME) FROM LIM_BIN.dbo.UL WHERE ID_UL=ID_IN)
            WHEN 2 THEN (SELECT NAME FROM ACT WHERE ID_ACT=ID_IN)
        END 
    WHEN UPPER(@ORDER_BY) IN ('A', 'ACCEPT') THEN 
        CASE 
            WHEN @ID_ARM=T1.ID_ARM THEN T1.ACCEPT_MASTER
            WHEN T3.DIVISION=T1.ID_SLAVE THEN T1.ACCEPT_SLAVE 
             ELSE '' 
        END 
    ELSE CAST(D_DOC AS VARCHAR(100))+STR(N_DOC, 20, 0)
    END AS ORDER_BY
FROM V_NOTE T1
LEFT JOIN NOTE_HAR T2 ON T2.ID_ARM=@ID_ARM AND T2.ID_NOTE_TYPE=T1.ID_NOTE_TYPE
LEFT JOIN ARM T3 ON T3.ID_ARM=@ID_ARM
WHERE (T2.CAN_READ = 2 OR (T2.CAN_READ=1 AND T3.DIVISION IN (T1.ID_OUT, T1.ID_IN)))
AND (@ID_NOTE_TYPE IS NULL OR @ID_NOTE_TYPE=T1.ID_NOTE_TYPE)
AND(@DATE_FROM IS NULL OR dbo.DATETIME_AS_DATE(@DATE_FROM)<=T1.D_DOC) 
AND (@DATE_TO IS NULL OR dbo.DATETIME_AS_DATE(@DATE_TO)>=T1.D_DOC)
AND (ISNULL(@OUT,0)<=0 OR ID_OUT=@OUT)
AND (ISNULL(@IN,0)<=0 OR ID_IN=@IN)
ORDER BY ORDER_BY
GO