troubleshooting Question

Dynamic SQL Stored Procedure Very Poor Performance

Avatar of monica73174
monica73174 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments2 Solutions406 ViewsLast Modified:
I have a stored procedure written in dynamic SQL using MS SQL Server that is taking over 6 minutes to run.  This was not the case just over a week ago and it took at the most 25 seconds to bring a data set back.  What do I need to post to make it easier to provide assistance for this problem?  I am really at a loss because nothing has changed.  I was also told the indexes get rebuilt every evening; and the stored procedure was dropped and recreated after the performance started to decline.  

This is the stored proc that uses the dynamic sql.  

ALTER PROCEDURE [dbo].[DOCUMENT_ACITIVITY_REPORT]
(
      @START_DATE datetime = NULL,
      @END_DATE datetime = NULL,
      @REPORT_TYPE VARCHAR(20) = NULL,
      @AFFILIATE_ID INT = 0,
      @COMPANY_NAME VARCHAR(20) = NULL,
      @COMPANY_CODE VARCHAR(20) = NULL,
      @READY_ONLY BIT=0,
      @JOB_ID INT=0,
      @JOB_ID_LIST VARCHAR(300) = NULL,
      @JOB_TYPE VARCHAR(20) = NULL,
      @PACKET_TYPE VARCHAR(10)= NULL,
      @phylink bit = 0,
       @DEBUG BIT=0 -- When set to 1 just prints SQL and does not execute it


)
AS
BEGIN

    DECLARE @sSql1 varchar(8000)
    DECLARE @sSql2 varchar(8000)
    DECLARE @sWhereClause varchar(8000)
    DECLARE @ParmDefinition varchar(8000)
    DECLARE @NewLine varchar(8000)
    DECLARE @EKG_TIMESTAMP char(1)
    SET @NewLine = CHAR(13) + CHAR(10)
    SET @EKG_TIMESTAMP = ''


   


    SET @sWhereClause = '' -- Initialise
   
    SET @sSql1 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ' +@NewLine
    SET @sSql1 = @sSql1 + 'SELECT' + @NewLine
    --SET @sSql1 = 'SELECT' + @NewLine
                        + '            DISTINCT' + @NewLine
            IF RTRIM(LTRIM(@REPORT_TYPE)) = 'Summary'
            BEGIN
                        SET @sSql1 = @sSql1
                        + '            AF.AFFILIATE_ID AS AFFILIATE,' + @NewLine
                        + '            AF.NAME,' + @NewLine
                        + '            J.JOB_ID,' + @NewLine
                        + '            J.JOB_TYPE,' + @NewLine
                        + '            J.CLIENT_DSCR_ID1,' + @NewLine
                        + '            C.CLIENT_NAME,' + @NewLine
                        + '            CL.LOCATION_NAME,' + @NewLine
                        + '            J.START_DATE,' + @NewLine
                        + '            J.END_DATE,' + @NewLine
                        + '            COUNT(DISTINCT JAT.PATIENT_CODE_ID) AS APPOINTMENTS_COUNT,' + @NewLine
                        + '            COUNT(DISTINCT DOC.PATIENT_CODE_ID) AS PRINTED_PACKETS' + @NewLine
            IF RTRIM(LTRIM(@PACKET_TYPE)) = 'DOC'
            BEGIN
                        SET @sSql1 = @sSql1
                        + '            ,COUNT(DISTINCT STAT.PATIENT_CODE_ID) AS PACKETS_WITH_ERRORS' + @NewLine
            END
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            ,SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND DOC_PRINT.PATIENT_CODE_ID IS NOT NULL THEN 1 ELSE 0 END) AS DOCLETTER_READY ' + @NewLine
                  END
                  IF @PACKET_TYPE = 'DOC' AND (RTRIM(LTRIM(@JOB_TYPE)) = 'STD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'RD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'ALL')
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            ,SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND ((IL.PATIENT_CODE_ID IS NOT NULL AND MH.PATIENT_CODE_ID IS NOT NULL )OR (MH.PATIENT_CODE_ID IS NULL AND LAB.B_ID IS NOT NULL )) AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL THEN 1 ELSE 0 END) AS DOCLETTER_READY ' + @NewLine  -- removed MHO.ID IS NULL
                  END
                  IF @PACKET_TYPE = 'PRE' AND (RTRIM(LTRIM(@JOB_TYPE)) = 'STD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'RD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'ALL')
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            ,SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL THEN 1 ELSE 0 END) AS DOCLETTER_READY ' + @NewLine
                  END
                  IF @PACKET_TYPE = 'DOC'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            ,COUNT(LAB.LW_UID) AS WARN,' + @NewLine
                        + '            COUNT(JE.ID) AS NO_EKG_IN_RANGE,' + @NewLine
                        + '            COUNT(NULLIF(JAT.PATIENT_CODE_ID, LAB.PATIENT_CODE_ID)) AS NO_LAB_IN_RANGE,' + @NewLine
                        + '            COUNT(NULLIF(JAT.PATIENT_CODE_ID, MH.PATIENT_CODE_ID)) AS NO_HISTORY_IN_RANGE,' + @NewLine
                        + '            SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND MH.PATIENT_CODE_ID IS NULL AND LAB.PATIENT_CODE_ID IS NOT NULL AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL AND MHO.ID IS NULL THEN 1 ELSE 0 END) AS OVERRIDE_COUNT' + @NewLine
                  END
            END
            IF (RTRIM(LTRIM(@REPORT_TYPE)) = 'APPMT_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'PRINT_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'READY_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'WARN_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_LAB_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_HIST_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_IHI_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'OVER_RIDE_COUNT'
                  OR RTRIM(LTRIM(@REPORT_TYPE)) = 'REVIEW_COUNT')
            BEGIN
                        SET @sSql1 = @sSql1
                        + '            P.SSN,' + @NewLine
                        + '            P.PATIENT_ID,' + @NewLine
                        + '            P.WORK_PHONE_EXT,' + @NewLine
                        + '     P.WORK_EXTENSION,' + @NewLine
                        + '            P.LAST_NAME,' + @NewLine
                        + '            P.FIRST_NAME,' + @NewLine
                        + '            P.BIRTH_DATE,' + @NewLine
                        + '            P.SEX,' + @NewLine
                        + '            C.CLIENT_NAME,' + @NewLine
                        + '            CL.LOCATION_NAME,' + @NewLine
                        + '            JAT.APPMT_DATE_TIME,' + @NewLine
                        + '            NULL AS MISSING,' + @NewLine
                        + '     PC.PATIENT_CODE,' + @NewLine
                        + '            PC.ID AS PATIENT_CODE_ID,' + @NewLine                        
                        + '            JAT.APPMT_DATE_TIME AS APPOINTMENT_TIMESTAMP,' + @NewLine
                        + '            J.CLIENT_DSCR_ID1 AS COMPANY,' + @NewLine
                        + '            1 AS PACKET_COUNT,' + @NewLine
                        + '            DOC.EVENT_START_TIMESTAMP' + @NewLine
                  IF @PACKET_TYPE = 'PRE'
                  BEGIN
                  SET @sSql1 = @sSql1            
                      + '            ,NULL AS LAB_TIMESTAMP' + @NewLine
                      + '            ,NULL AS HISTORY_IMPORT_TIMESTAMP' + @NewLine
                      + '            ,NULL AS EKG_TIMESTAMP' + @NewLine
                  END
                  IF @PACKET_TYPE = 'DOC'
                  BEGIN
                  SET @sSql1 = @sSql1                  
                              + '            ,LAB.LR_UID,' + @NewLine
                              + '            LAB.LR_DRAWN_DATE AS LAB_TIMESTAMP,' + @NewLine
                              + '            NULL AS EKG_TIMESTAMP,' + @NewLine
                              + '            (SELECT TOP 1 IMPORT_TIMESTAMP FROM MEDACCESS_IMPORT_TEST_DB.DBO.I_MEDICALHISTORY M' + @NewLine
                              + '     WHERE PATIENT_CODE_ID = PC.ID) AS HISTORY_IMPORT_TIMESTAMP,' + @NewLine
                              + '            LAB.LR_SPECIMEN_TEXT,' + @NewLine
                              + '            LAB_NAME.LAB_NAME,' + @NewLine
                              + '            LAB.LR_LAB,' + @NewLine
                              + '            NULL AS NO_IHI_IN_RANGE,' + @NewLine
                              + '            NULL AS NO_LAB_IN_RANGE,' + @NewLine
                              + '            NULL AS NO_HISTORY_IN_RANGE' + @NewLine
                  END
                              
            END

                        SET @sSql1 = @sSql1
                        + 'FROM' + @NewLine      
                        + '            JOB J' + @NewLine
                        + '            INNER JOIN' + @NewLine
                        + '                  CLIENT C' + @NewLine
                        + '            ON' + @NewLine
                        + '                  C.CLIENT_ID = J.CLIENT_ID' + @NewLine
                        + '            INNER JOIN' + @NewLine
                        + '                  JOB_APPOINTMENT JAT' + @NewLine
                        + '            ON' + @NewLine
                        + '                  JAT.JOB_ID = J.JOB_ID' + @NewLine
                        --+ '                  AND JAT.DELETED IS NULL' + @NewLine
                        --+ '                  AND JAT.BILLING_EXCLUDE IS NULL' + @NewLine
                        + '                  AND JAT.APPMT_STATUS = ' + '''' + '''' + 'O' + '''' + '''' + @NewLine
                        + '                  AND JAT.LAB_SERVICE_APPOINTMENT <> ' + '''' + '''' + 'N' + '''' + '''' + @NewLine
                        + '                  AND JAT.PATIENT_ID <> 0' + @NewLine
                        + '                  AND JAT.PATIENT_ID IS NOT NULL' + @NewLine
                        + '                  AND (JAT.BLOCKED <> ' + '''' + '''' + 'Y' + '''' + '''' + @NewLine
                        + '         OR JAT.BLOCKED IS NULL)' + @NewLine
                        + '            INNER JOIN' + @NewLine
                        + '     CLIENT_LOCATION CL' + @NewLine
                        + '            ON' + @NewLine
                        + '     J.CLIENT_DSCR_ID1 = CL.CLIENT_LOCATION_ID' + @NewLine
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'ALL'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND ' + @NewLine
                        + '                  (' + @NewLine
                        + '                        (' + @NewLine
                        + '                        JAT.APPMT_DATE_TIME >= @START_DATE' + @NewLine
                        + '                        AND JAT.APPMT_DATE_TIME < DATEADD(d, 1, @END_DATE)' + @NewLine
                        + '                        )' + @NewLine
                        + '                   OR ' + @NewLine
                        + '                        (' + @NewLine
                        + '              J.START_DATE >= @START_DATE' + @NewLine
                        + '                         AND J.START_DATE < DATEADD(d, 1, @END_DATE)' + @NewLine
                        + '                        )' + @NewLine
                        + '          )'
                        
                  END
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'STD'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND J.START_DATE >= @START_DATE' + @NewLine
                        + '                  AND J.START_DATE < DATEADD(d, 1, @END_DATE)' + @NewLine
                        + '                  AND (J.JOB_TYPE = ' + '''' + '''' + 'STD' + '''' + '''' + ')' + @NewLine
                  
                  END
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'RD'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JAT.APPMT_DATE_TIME >= @START_DATE' + @NewLine
                        + '                  AND JAT.APPMT_DATE_TIME < DATEADD(d, 1, @END_DATE)' + @NewLine
                        + '                  AND (J.JOB_TYPE = ' + '''' + '''' + 'RD' + '''' + '''' + ')' + @NewLine
                        IF RTRIM(LTRIM(@phylink)) = '1'
                        BEGIN
                        SET @sSql1 = @sSql1
                              + '     AND CL.PL_YES = 1'
                        END
                        IF RTRIM(LTRIM(@phylink)) = '0'
                        BEGIN
                        SET @sSql1 = @sSql1
                              + '     AND (CL.PL_YES = 0 OR CL.PL_YES IS NULL)' + @NewLine
                        END
                  END
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND J.START_DATE >= @START_DATE' + @NewLine
                        + '                  AND J.START_DATE < DATEADD(d, 1, @END_DATE)' + @NewLine
                        + '                  AND (J.JOB_TYPE = ' + '''' + '''' + 'STD' + '''' + '''' + 'OR J.JOB_TYPE = ' + '''' + '''' + 'RD' + '''' + '''' + ')' + @NewLine
                  END
                        SET @sSql1 = @sSql1
                        + '            INNER JOIN' + @NewLine
                        + '                  PATIENT P' + @NewLine
                        + '            ON' + @NewLine
                        + '                  P.PATIENT_ID = JAT.PATIENT_ID' + @NewLine
                        + '            INNER JOIN' + @NewLine
                        + '                  PATIENT_CODE PC' + @NewLine
                        + '            ON' + @NewLine
                        + '                  PC.ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              MIN(ACR.AFFILIATE_ID) AS AFFILIATE_ID,' + @NewLine
                        + '                                    (' + @NewLine
                        + '                                          SELECT' + @NewLine
                        + '                                                A.NAME' + @NewLine
                        + '                                          FROM' + @NewLine
                        + '                                                AFFILIATE A' + @NewLine
                        + '                                          WHERE' + @NewLine
                        + '                                                A.ID = MIN(ACR.AFFILIATE_ID)' + @NewLine
                        + '                                    ) AS NAME,' + @NewLine
                        + '                                    ACR.CLIENT_ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              AFFILIATE_CLIENT_REL ACR' + @NewLine
                        + '                        GROUP BY' + @NewLine
                        + '                              ACR.CLIENT_ID' + @NewLine
                        + '                  ) AS AF' + @NewLine
                        + '            ON' + @NewLine
                        + '                  C.CLIENT_ID = AF.CLIENT_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DR.PATIENT_CODE_ID,' + @NewLine
                        + '                              MAX(DEL.EVENT_START_TIMESTAMP) AS EVENT_START_TIMESTAMP' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              DOCUMENT_RUN DR' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              DOCUMENT_EVENT_LOG_DETAIL DELD' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              DR.DOCUMENT_EVENT_LOG_DETAIL_ID = DELD.ID' + @NewLine
                        + '                              AND DELD.DELETED IS NULL' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              DOCUMENT_EVENT_LOG DEL' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              DEL.ID = DELD.DOCUMENT_EVENT_LOG_ID' + @NewLine
                        + '                              AND DEL.DELETED IS NULL' + @NewLine
                        + '                              --AND DEL.EVENT_STATUS_ID = 9' + @NewLine
                  IF @PACKET_TYPE = 'DOC' AND (RTRIM(LTRIM(@JOB_TYPE)) = 'STD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'RD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'ALL')
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                              AND DEL.DOCUMENT_TEMPLATE_ID = 1' + @NewLine        
                  END
                  IF @PACKET_TYPE = 'PRE' AND (RTRIM(LTRIM(@JOB_TYPE)) = 'STD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'RD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'ALL')
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                              INNER JOIN ' + @NewLine  
                        + '                              DOCUMENT_TEMPLATE DTEMP ' + @NewLine
                        + '                              ON ' + @NewLine
                        + '                              DTEMP.ID = DEL.DOCUMENT_TEMPLATE_ID ' + @NewLine         
                        + '                              INNER JOIN ' + @NewLine         
                        + '                              DOCUMENT_TYPE DT ' + @NewLine         
                        + '                              ON ' + @NewLine
                        + '                              DT.ID = DTEMP.DOCUMENT_TYPE_ID ' + @NewLine         
                        + '                              AND DT.ID IN (3,4,5,6) ' + @NewLine       
                  END
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                              AND DEL.DOCUMENT_TEMPLATE_ID = 7' + @NewLine
                  END
                        SET @sSql1 = @sSql1        
                        + '                        WHERE' + @NewLine
                        + '                              DR.DELETED IS NULL' + @NewLine
                        + '                              AND DR.PDF_COMPOSE_END_TIMESTAMP IS NOT NULL' + @NewLine
                        + '                        GROUP BY DR.PATIENT_CODE_ID' + @NewLine
                        + '                  ) AS DOC' + @NewLine
                        + '            ON' + @NewLine
                        + '                  DOC.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                  IF RTRIM(LTRIM(@JOB_TYPE)) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              DR.PATIENT_CODE_ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              DOCUMENT_RUN DR' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              DOCUMENT_EVENT_LOG_DETAIL DELD' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              DR.DOCUMENT_EVENT_LOG_DETAIL_ID = DELD.ID' + @NewLine
                        + '                              AND DELD.DELETED IS NULL' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              DOCUMENT_EVENT_LOG DEL' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              DEL.ID = DELD.DOCUMENT_EVENT_LOG_ID' + @NewLine
                        + '                              AND DEL.DELETED IS NULL' + @NewLine
                        + '                              AND DEL.DOCUMENT_TEMPLATE_ID = 1' + @NewLine        
                        + '                        WHERE' + @NewLine
                        + '                              DR.DELETED IS NULL' + @NewLine
                        + '                              AND DR.PDF_COMPOSE_END_TIMESTAMP IS NOT NULL' + @NewLine
                        + '                  ) AS DOC_PRINT' + @NewLine
                        + '            ON' + @NewLine
                        + '                  DOC_PRINT.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                  END
                  IF @PACKET_TYPE = 'DOC'
                  BEGIN
                        SET @sSql1 = @sSql1        
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              PC.ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              PATIENT_CODE PC' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              PATIENT_RERUN_QUAL PRC' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              PRC.PATIENT_ID = PC.PATIENT_ID' + @NewLine
                        + '                              AND PC.DELETED IS NULL' + @NewLine
                        + '                              AND PRC.QUAL_RUN_END_TIMESTAMP IS NULL' + @NewLine
                        + '                              AND (PRC.ERROR_FLAG IS NULL OR PRC.ERROR_FLAG = 0)' + @NewLine
                        + '                  ) AS PRQ' + @NewLine
                        + '            ON' + @NewLine
                        + '                  PRQ.ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              DES.PATIENT_CODE_ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              DOCUMENT_EVENT_STATUS DES' + @NewLine
                        + '                        WHERE' + @NewLine
                        + '                              DES.DELETED IS NULL' + @NewLine
                        + '                              AND DES.STATUS = ' + '''' + '''' + 'Review' + '''' + ''''+ @NewLine
                        + '                 --AND DES.PATIENT_CODE_ID NOT IN (SELECT PATIENT_CODE_ID FROM DOCUMENT_EVENT_STATUS WHERE STATUS = ' + '''' + '''' + 'Approve' + '''' + ''''+ ')'+ @NewLine
                        + '                  ) AS STAT' + @NewLine
                        + '            ON' + @NewLine
                        + '                  STAT.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID'+ @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                      + '         ( ' + @NewLine
                        + '            SELECT ' + @NewLine
                        + '                 DISTINCT ' + @NewLine
                        + '                 IL.PATIENT_CODE_ID ' + @NewLine
                        + '            FROM ' + @NewLine
                        + '                 MEDACCESS_IMPORT_TEST_DB.DBO.i_LabReport IL ' + @NewLine
                        + '            WHERE IL.LR_CONVERTED = 1 ' + @NewLine
                      + '          ) AS IL ' + @NewLine
                    + '     ON ' + @NewLine
                      + '     IL.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID ' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              IL.PATIENT_CODE_ID,' + @NewLine
                        + '                              MAX(IL.LR_UID) AS LR_UID,' + @NewLine
                        + '                              MAX(IL.LR_DRAWN_DATE) AS LR_DRAWN_DATE,' + @NewLine
                        + '                              MAX(IL.LR_SPECIMEN_TEXT) AS LR_SPECIMEN_TEXT,' + @NewLine
                        + '                              MAX(IL.LR_LAB) AS LR_LAB,' + @NewLine
                        + '                              MAX(ILW.LW_UID) AS LW_UID,' + @NewLine
                        + '                              MAX(IB.B_ID) AS B_ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.I_LABREPORT IL' + @NewLine
                        + '                        LEFT OUTER JOIN' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.I_LABREPORT_WARNINGS ILW' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              IL.LR_UID = ILW.LW_UID AND IL.LR_CONVERTED = 1' + @NewLine
                        + '                        LEFT OUTER JOIN' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.I_BATCH IB' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              IB.B_ID = IL.LR_BATCH AND CURRENT_TIMESTAMP>= DATEADD(D, 10, IB.B_IMPORT_DATE)' + @NewLine
                        + '                        WHERE' + @NewLine
                        + '                              IL.LR_CONVERTED = 1' + @NewLine
                        + '                        GROUP BY' + @NewLine
                        + '                              IL.PATIENT_CODE_ID' + @NewLine
                        + '                  ) AS LAB' + @NewLine
                        + '            ON' + @NewLine
                        + '                  LAB.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              IL.PATIENT_CODE_ID,' + @NewLine
                        + '                              C.LAB_NAME' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.I_LABREPORT IL' + @NewLine
                        + '                        INNER JOIN' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.C_LABS C' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              C.ID = IL.LR_LAB' + @NewLine
                        + '                  ) AS LAB_NAME' + @NewLine
                        + '            ON' + @NewLine
                        + '                  LAB_NAME.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              IM.PATIENT_CODE_ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              MEDACCESS_IMPORT_TEST_DB.DBO.I_MEDICALHISTORY IM' + @NewLine
                        + '                        WHERE ' + @NewLine
                        + '                              IM.MH_CONVERTED = 1 ' + @NewLine
                        + '                              AND IM.IMPORT_TABLE_ID <> ' + '''' + '''' + '99999' + '''' + '''' + @NewLine
                        + '                  ) AS MH' + @NewLine
                        + '            ON' + @NewLine
                        + '                  MH.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                        + '                  (' + @NewLine
                        + '                        SELECT' + @NewLine
                        + '                              DISTINCT' + @NewLine
                        + '                              PC.ID' + @NewLine
                        + '                        FROM' + @NewLine
                        + '                              PATIENT_CODE PC' + @NewLine
                        + '                        LEFT OUTER JOIN' + @NewLine
                        + '                              IHI_TRACKING IT' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              IT.PATIENT_CODE_ID = PC.ID' + @NewLine
                        + '                              AND IT.DELETED IS NULL' + @NewLine
                        + '                              AND PC.DELETED IS NULL' + @NewLine
                        + '                        LEFT OUTER JOIN' + @NewLine
                        + '                              IHI_TRACKING_JOB_EXCLUDE ITJE' + @NewLine
                        + '                        ON' + @NewLine
                        + '                              ITJE.JOB_ID = PC.JOB_ID AND ITJE.DELETED IS NULL AND PC.DELETED IS NULL' + @NewLine
                        + '                        WHERE' + @NewLine
                        + '                              IT.PATIENT_CODE_ID IS NULL' + @NewLine
                        + '                              AND ITJE.JOB_ID IS NULL' + @NewLine
                        + '                  ) AS JE' + @NewLine
                        + '            ON' + @NewLine
                        + '                  JE.ID = JAT.PATIENT_CODE_ID' + @NewLine
                        + '            LEFT OUTER JOIN' + @NewLine
                         + '                  MEDACCESS_IMPORT_TEST_DB.DBO.MEDICAL_HISTORY_OVERRIDE MHO' + @NewLine
                        + '            ON' + @NewLine
                        + '                  MHO.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID' + @NewLine
                        END
                  IF @COMPANY_CODE IS NOT NULL AND RTRIM(LTRIM(@COMPANY_CODE)) <> ''
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            INNER JOIN' + @NewLine
                        + '                  dbo.iter$simple_intlist_to_tbl(@COMPANY_CODE) LI' + @NewLine
                        + '            ON'  + @NewLine
                        + '                  J.CLIENT_DSCR_ID1 = LI.NUMBER'  + @NewLine
                  END
                  --IF @JOB_ID_LIST IS NOT NULL AND RTRIM(LTRIM(@JOB_ID_LIST)) <> ''
                  --BEGIN
                  --      SET @sSql1 = @sSql1
                  --      + '            INNER JOIN' + @NewLine
                  --      + '                  dbo.iter$simple_intlist_to_tbl(@JOB_ID_LIST) JL' + @NewLine
                  --      + '            ON'  + @NewLine
                  --      + '                  J.JOB_ID = JL.NUMBER'  + @NewLine
                  --      END
                        SET @sSql1 = @sSql1
                        + '            WHERE' + @NewLine
                        + '                  1 = 1'  + @NewLine
                  IF @JOB_ID IS NOT NULL AND @JOB_ID <> 0
                  BEGIN
                   SET @sSql1 = @sSql1
                   + '                  AND J.JOB_ID = @JOB_ID' + @NewLine
                  END
                  IF @JOB_ID_LIST <> '0' AND @JOB_ID_LIST IS NOT NULL AND RTRIM(LTRIM(@JOB_ID_LIST)) <> ''
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND J.JOB_ID IN (SELECT NUMBER FROM iter$simple_intlist_to_tbl(@JOB_ID_LIST))' + @NewLine
                        END
                  
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'PRINT_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND DOC.PATIENT_CODE_ID IS NOT NULL ' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'READY_COUNT' AND @PACKET_TYPE = 'DOC' AND RTRIM(@JOB_TYPE) <> 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND ((IL.PATIENT_CODE_ID IS NOT NULL AND MH.PATIENT_CODE_ID IS NOT NULL )OR (MH.PATIENT_CODE_ID IS NULL AND LAB.B_ID IS NOT NULL ))  AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL ' + @NewLine -- Removed on October 10 2011 AND MHO.ID IS NULL
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'READY_COUNT' AND @PACKET_TYPE = 'DOC' AND RTRIM(@JOB_TYPE) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND ((IL.PATIENT_CODE_ID IS NOT NULL AND MH.PATIENT_CODE_ID IS NOT NULL )OR (MH.PATIENT_CODE_ID IS NULL AND LAB.B_ID IS NOT NULL ))  AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL ' + @NewLine -- Removed on October 10 2011 AND MHO.ID IS NULL
                        + '            ORDER BY P.LAST_NAME, P.FIRST_NAME ' + @NewLine -- Removed on October 10 2011 AND MHO.ID IS NULL
                  END

                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'READY_COUNT' AND @PACKET_TYPE = 'PRE'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'WARN_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND LAB.LW_UID IS NOT NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_LAB_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND NULLIF(JAT.PATIENT_CODE_ID, LAB.PATIENT_CODE_ID) IS NOT NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_HIST_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND NULLIF(JAT.PATIENT_CODE_ID, MH.PATIENT_CODE_ID) IS NOT NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'MISSING_IHI_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JE.ID IS NOT NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'OVER_RIDE_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND MH.PATIENT_CODE_ID IS NULL AND LAB.PATIENT_CODE_ID IS NOT NULL AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL AND MHO.ID IS NULL' + @NewLine
                  END
                  IF RTRIM(LTRIM(@REPORT_TYPE)) = 'REVIEW_COUNT'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND STAT.PATIENT_CODE_ID = PC.ID' + @NewLine
                  END
                  IF @COMPANY_NAME IS NOT NULL AND RTRIM(LTRIM(@COMPANY_NAME)) <> ''
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND C.CLIENT_NAME LIKE ' + '''' + '''' + '%' + '''' + '''' + ' + ' + '@COMPANY_NAME' + ' + ' + '''' + '''' + '%' + '''' + '''' + @NewLine
                        END
                  IF @AFFILIATE_ID IS NOT NULL AND @AFFILIATE_ID <> 0
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '                  AND AF.AFFILIATE_ID = @AFFILIATE_ID' + @NewLine
                  END
            IF RTRIM(LTRIM(@REPORT_TYPE)) = 'Summary'
            BEGIN
                        SET @sSql1 = @sSql1
                        + '            GROUP BY' + @NewLine
                        + '                  J.JOB_ID,' + @NewLine
                        + '                  J.CLIENT_DSCR_ID1,' + @NewLine
                        + '                  C.CLIENT_NAME,' + @NewLine
                        + '                  J.START_DATE,' + @NewLine
                        + '                  J.JOB_TYPE,' + @NewLine
                        + '                  J.END_DATE,' + @NewLine
                        + '                  AF.AFFILIATE_ID,' + @NewLine
                        + '                  AF.NAME,' + @NewLine
                        + '                CL.LOCATION_NAME' + @NewLine
                  IF @READY_ONLY = 1 AND (RTRIM(LTRIM(@JOB_TYPE)) = 'STD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'RD' OR RTRIM(LTRIM(@JOB_TYPE)) = 'ALL')
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            HAVING ' + @NewLine
                        + '                  SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND ((IL.PATIENT_CODE_ID IS NOT NULL AND MH.PATIENT_CODE_ID IS NOT NULL )OR (MH.PATIENT_CODE_ID IS NULL AND LAB.B_ID IS NOT NULL ))  AND JE.ID IS NULL AND PRQ.ID IS NULL AND STAT.PATIENT_CODE_ID IS NULL AND MHO.ID IS NULL THEN 1 ELSE 0 END) > 0' + @NewLine
                  END
                  IF @READY_ONLY = 1 AND RTRIM(LTRIM(@JOB_TYPE)) = 'HICFA'
                  BEGIN
                        SET @sSql1 = @sSql1
                        + '            HAVING ' + @NewLine
                        + '                  SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND DOC_PRINT.PATIENT_CODE_ID IS NOT NULL THEN 1 ELSE 0 END) > 0' + @NewLine
                  END

                        IF @PACKET_TYPE = 'DOC'
                        BEGIN
                        SET @sSql1 = @sSql1
                        + '            ORDER BY' + @NewLine
                        + '                  J.START_DATE,' + @NewLine
                        + '                  J.END_DATE,' + @NewLine
                        + '                  J.JOB_ID DESC' + @NewLine
                        END
                        IF @PACKET_TYPE = 'PRE'
                        BEGIN
                        SET @sSql1 = @sSql1
                        + '            ORDER BY' + @NewLine
                        + '                  J.START_DATE DESC,' + @NewLine
                        + '                  J.END_DATE,' + @NewLine
                        + '                  J.JOB_ID DESC'
                        END

            END

 
 
 
 
    SET @ParmDefinition = '@START_DATE datetime, ' + @NewLine
                        + '@END_DATE datetime, ' + @NewLine
                        + '@JOB_TYPE VARCHAR(20), ' + @NewLine
                        + '@COMPANY_CODE VARCHAR(20), ' + @NewLine
                        + '@AFFILIATE_ID VARCHAR(20), ' + @NewLine
                        + '@COMPANY_NAME VARCHAR(20), ' + @NewLine
                        + '@JOB_ID INT, ' + @NewLine
                        + '@JOB_ID_LIST VARCHAR(300), ' + @NewLine
                        + '@READY_ONLY BIT,' + @NewLine
                        + '@REPORT_TYPE VARCHAR(20)'
                       

    --SET @sSql1 = @sSql  @sWhereClause
    IF @Debug = 0



     exec('EXEC sp_executesql N' + '''' + @sSql1 + '''' + ', ' + 'N' + '''' + @ParmDefinition + ''''  + ', ' + @NewLine
                                                + '@START_DATE =' + '''' + @START_DATE + '''' + ', ' + @NewLine
                                                --+ '@START_DATE =' + '''' + Convert(varchar(50), @START_DATE, 121) + ''''  + ', ' + @NewLine
                                                + '@END_DATE =' + '''' + @END_DATE + '''' + ', ' + @NewLine
                                                --+ '@END_DATE =' + '''' + Convert(varchar(50), @END_DATE, 121) + '''' + ', ' + @NewLine
                                                + '@JOB_TYPE =' + '''' + @JOB_TYPE + ''''  + ', ' + @NewLine
                                                --'@COMPANY_CODE =' + Cast(@COMPANY_CODE AS varchar(30)) + ', ' +
                                                + '@COMPANY_CODE =' + '''' + @COMPANY_CODE + '''' + ', ' + @NewLine
                                              + '@AFFILIATE_ID =' + '''' + @AFFILIATE_ID + '''' + ', ' + @NewLine
                                              --'@AFFILIATE_ID =' + '''' + CAST(@AFFILIATE_ID AS VARCHAR(30)) + '''' + ', ' +
                                                --+ '@COMPANY_NAME = ' + ISNULL('''' + CAST(@COMPANY_NAME AS VARCHAR(20)) + '''', 'NULL'))
                                                + '@COMPANY_NAME = ' + '''' + @COMPANY_NAME + ''''  + ', ' + @NewLine
                                                + '@REPORT_TYPE =' + '''' + @REPORT_TYPE + ''''  + ', ' + @NewLine
                                                + '@READY_ONLY =' + @READY_ONLY + ', ' + @NewLine
                                                + '@JOB_ID_LIST =' + '''' + @JOB_ID_LIST + '''' + ', ' + @NewLine
                                                + '@JOB_ID =' + @JOB_ID)
                                                
                                                
    ELSE
        BEGIN
            PRINT 'DECLARE ' + @ParmDefinition
            PRINT ''
                  PRINT 'SET @START_DATE = ' + IsNull('''' + Convert(varchar(50), @START_DATE, 121) + '''','NULL')
            PRINT 'SET @END_DATE = ' + IsNull('''' + Convert(varchar(50), @END_DATE, 121) + '''','NULL')
            PRINT 'SET @REPORT_TYPE = ' + IsNull('''' + @REPORT_TYPE + '''','NULL')
                  PRINT 'SET @AFFILIATE_ID = ' + IsNull('''' + CAST(@AFFILIATE_ID AS VARCHAR(30)) + '''','NULL')
                  PRINT 'SET @COMPANY_NAME = ' + IsNull('''' + @COMPANY_NAME + '''','NULL')
                  PRINT 'SET @COMPANY_CODE = ' + IsNull('''' + @COMPANY_CODE + '''','NULL')
                  PRINT 'SET @READY_ONLY = ' + IsNull('''' + CAST(@READY_ONLY AS VARCHAR(1)) + '''','NULL')
                  PRINT 'SET @JOB_ID = ' + IsNull('''' + CAST(@JOB_ID AS VARCHAR(30)) + '''','NULL')
                  PRINT 'SET @JOB_ID_LIST = ' + IsNull('''' + @JOB_ID_LIST + '''','NULL')
                  PRINT 'SET @JOB_TYPE = ' + IsNull('''' + @JOB_TYPE + '''','NULL')
            PRINT ''
            PRINT REPLACE(@sSql1, '''''', '''')
           
           
        END

    --END IF



END
ASKER CERTIFIED SOLUTION
Eyal

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros