|
Вот текст SP, хотя без структуры базы он, наверное, мало что значит.
Вопрос-то не по синтаксису, а в ппринципе, что есть SP в понимании
Access.
Access у меня нет, и не будет. Soft только лицензионный.
CREATE PROC DEMO (@reg int, @typ int, @vid1 int, @vid2 int, @vid3 int, @status1 int, @status2 int, @status3 int,
@prm1 int, @prm2 int, @prm3 int, @prm4 int, @prm5 int, @prm6 int, @prm7 int, @prm8 int, @prm9 int, @prm10 int)
AS
SELECT OBJ.OBJ_ID, OBJ.OBJ_REG, OBJ.OBJ_TYP, OBJ.OBJ_NAME, RG.NAME, VAL1.VAL_NAME, PRM1.PRM_VAL, PRM10.PRM_VAL, PRM2.PRM_VAL, PRM3.PRM_VAL, VAL2.VAL_NAME, VAL3.VAL_NAME, VAL4.VAL_NAME, VAL5.VAL_NAME, OBJ.OBJ_DTIN, VAL6.VAL_NAME, OBJ.OBJ_DTOUT, PRM4.PRM_VAL, PRM5.PRM_VAL, PRM6.PRM_VAL, PRM7.PRM_VAL, PRM8.PRM_VAL, PRM9.PRM_VAL
FROM ((((((((((((((((OBJ INNER JOIN RG ON OBJ.OBJ_ZONE=RG.CODE)
INNER JOIN VAL VAL1 ON (VAL1.VAL_REG = OBJ.OBJ_ZONE) AND (VAL1.VAL_CODE=OBJ.OBJ_PLACE) AND (VAL1.VAL_SPR = OBJ.[OBJ_#PLACE]))
INNER JOIN VAL VAL2 ON (VAL2.VAL_REG = OBJ.OBJ_REG) AND (VAL2.VAL_CODE=OBJ.OBJ_TYP) AND (VAL2.VAL_SPR = OBJ.[OBJ_#TYP]))
INNER JOIN VAL VAL3 ON (VAL3.VAL_REG = OBJ.OBJ_REG) AND (VAL3.VAL_CODE = OBJ.OBJ_VID) AND (VAL3.VAL_SPR = OBJ.[OBJ_#VID]))
INNER JOIN VAL VAL4 ON (VAL4.VAL_REG = OBJ.OBJ_REG) AND (VAL4.VAL_CODE = OBJ.OBJ_STATUS) AND (VAL4.VAL_SPR = OBJ.[OBJ_#STATUS]))
INNER JOIN VAL VAL5 ON (VAL5.VAL_REG = OBJ.OBJ_REG) AND (VAL5.VAL_CODE = OBJ.OBJ_PRIN) AND (VAL5.VAL_SPR = OBJ.[OBJ_#PRIN]))
INNER JOIN VAL VAL6 ON (VAL6.VAL_REG = OBJ.OBJ_REG) AND (VAL6.VAL_CODE = OBJ.OBJ_PROUT) AND (VAL6.VAL_SPR = OBJ.[OBJ_#PROUT]))
LEFT JOIN (SELECT PRM_REG, PRM_ID, PRM_VAL FROM PRM WHERE PARAM_CODE = @prm1) PRM1 ON (PRM1.PRM_REG = OBJ.OBJ_REG) AND (PRM1.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM_REG, PRM_ID, PRM_VAL FROM PRM WHERE PARAM_CODE = @prm8) PRM8 ON (PRM8.PRM_REG = OBJ.OBJ_REG) AND (PRM8.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM_REG, PRM_ID, PRM_VAL FROM PRM WHERE PARAM_CODE = @prm9) PRM9 ON (PRM9.PRM_REG = OBJ.OBJ_REG) AND (PRM9.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM_REG, PRM_ID, PRM_VAL FROM PRM WHERE PARAM_CODE = @prm10) PRM10 ON (PRM10.PRM_REG = OBJ.OBJ_REG) AND (PRM10.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm2) PRM2
ON (PRM2.PRM_REG = OBJ.OBJ_REG) AND (PRM2.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm3) PRM3
ON (PRM3.PRM_REG = OBJ.OBJ_REG) AND (PRM3.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm4) PRM4
ON (PRM4.PRM_REG = OBJ.OBJ_REG) AND (PRM4.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm5) PRM5
ON (PRM5.PRM_REG = OBJ.OBJ_REG) AND (PRM5.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm6) PRM6
ON (PRM6.PRM_REG = OBJ.OBJ_REG) AND (PRM6.PRM_ID = OBJ.OBJ_ID))
LEFT JOIN (SELECT PRM.PRM_REG, PRM.PRM_ID, VAL.VAL_NAME AS [PRM_VAL]
FROM (PRM INNER JOIN PARAMS ON (PARAMS.REG = PRM.PRM_REG) AND (PARAMS.CODE = PRM.PARAM_CODE))
INNER JOIN VAL ON (VAL.VAL_REG = PRM.PRM_REG) AND (VAL.VAL_SPR = PARAMS.SPR) AND (VAL.VAL_CODE = PRM.PRM_VAL_SPR)
WHERE PRM.PARAM_CODE = @prm7) PRM7
ON (PRM7.PRM_REG = OBJ.OBJ_REG) AND (PRM7.PRM_ID = OBJ.OBJ_ID)
WHERE OBJ.OBJ_REG = @reg AND OBJ.OBJ_TYP = @typ AND (OBJ.OBJ_VID = @vid1 OR OBJ.OBJ_VID = @vid2 OR OBJ.OBJ_VID = @vid3)
AND (OBJ.OBJ_STATUS = @status1 OR OBJ.OBJ_STATUS = @status2 OR OBJ.OBJ_STATUS = @status3)
|
