vendredi 26 juin 2020

SQL IF inside IF

I have an error when I try to run this query. I tried to implement it in the proper way. Could you help me, please?

Error: Incorrect syntax near the keyword 'END'.

Query:

DECLARE @PROCESS_CODE NVARCHAR(50)
SELECT @PROCESS_CODE = px.process_code
FROM t_wfl_process_x px
WHERE px.tdesc_name = 't_bpm_process_step' AND px.px_type='multi_scoring'

DECLARE @CONTACT_BUYER INT
SELECT @CONTACT_BUYER = login.contact_id 
FROM t_bpm_process_step AS bpmstep
JOIN t_bpm_process AS bpm ON bpm.bpm_id =bpmstep.bpm_id
JOIN t_usr_login_securable AS securable ON securable.sec_id =bpm.sec_id
JOIN t_usr_login AS login ON login.login_name =securable.login_name
JOIN t_rfp_request AS rfp ON rfp.bpm_id=bpm.bpm_id
JOIN t_rfp_proposal prop ON rfp.rfp_id=prop.rfp_id
WHERE securable.profil_code ='bpm_tech'
AND prop.prop_id=@prop_id

DECLARE @PSTEP_ID INT
SELECT @PSTEP_ID = bpmstep.pstep_id
FROM t_bpm_process_step AS bpmstep
JOIN t_bpm_process AS bpm ON bpm.bpm_id =bpmstep.bpm_id
JOIN t_rfp_request AS rfp ON rfp.bpm_id=bpm.bpm_id
JOIN t_rfp_proposal prop ON rfp.rfp_id=prop.rfp_id
AND prop.prop_id=@prop_id




IF EXISTS 
(SELECT 1 
 FROM t_bpm_process AS bpm
 JOIN t_rfp_request AS rfp ON rfp.bpm_id=bpm.bpm_id 
 JOIN t_rfp_proposal AS prop ON prop.rfp_id=rfp.rfp_id
 WHERE bpm._bpm_tech_approve=1
 AND prop.prop_id=@prop_id)
BEGIN
  IF NOT EXISTS (SELECT 1 
      FROM t_wfl_process_execution AS pex
      WHERE pex.x_id=@PSTEP_ID
      AND pex.process_code=@PROCESS_CODE
      AND pex.tdesc_name='t_bpm_process_step')
  BEGIN
INSERT INTO t_wfl_process_execution
        (
        tdesc_name    
        ,process_code
        ,x_id
        ,begin_date
        ,contact_id_requester
        )

        SELECT
         't_bpm_process_step' 
        , @PROCESS_CODE process_code
        ,@PSTEP_ID x_id
        ,@timestamp begin_date
        ,@CONTACT_BUYER contact_id_requester

    



    INSERT INTO t_wfl_worklist
    (
        process_code
        ,x_id
        ,tdesc_name
        ,act_code
        ,contact_id_performer
        ,wli_date_ini
        ,contact_id_origin
        ,act_id
        ,pex_id
    )
    SELECT @PROCESS_CODE process_code
    ,@PSTEP_ID x_id
        ,'t_bpm_process_step' tdesc_name
        ,'INI' act_code
        ,@CONTACT_BUYER contact_id_performer
        ,@timestamp wli_date_ini
        ,@CONTACT_BUYER contact_id_origin
        ,act.act_id act_id
        ,pex.pex_id pex_id
    FROM t_wfl_process_execution pex
    JOIN t_wfl_activity act ON act.process_code = pex.process_code
    WHERE pex.process_code = @PROCESS_CODE
    AND pex.tdesc_name = 't_bpm_process_step'
    AND act.act_code = 'INI'
    AND NOT EXISTS 
    (
      SELECT 1 
      FROM t_wfl_process_execution AS pexec
      JOIN t_wfl_activity act ON act.process_code = pex.process_code
      WHERE pex.process_code = @PROCESS_CODE
      AND pex.tdesc_name = 't_bpm_process_step'
    AND act.act_code = 'INI'
     AND pexec.x_id=@PSTEP_ID
  END
  else SELECT 3
END ELSE SELECT 4

I don't know how to implement it. I have to write more information, but I don't know what else should I write here...

I have to write more information, but I don't know what else should I write here... I have to write more information, but I don't know what else should I write here... I have to write more information, but I don't know what else should I write here...

Aucun commentaire:

Enregistrer un commentaire