Error when populating temp table with query results

Question

Why does this query run alone ok but I get an error when I try to put the results in a temp table

Works

SELECT DISTINCT
ENCOUNTER_ID        = OP.PAT_ENC_CSN_ID
,NOTE_TYPE          = OP.PROC_CODE
,CREATE_TIME        = OP.ORDER_INST
,OP.ORDER_PROC_ID
,EAP.PROC_NAME
-----INTO #ECHO_ORDERS
FROM PAT_ENC E 
INNER JOIN #SER SER ON SER.PROV_ID = E.VISIT_PROV_ID
INNER JOIN ORDER_PROC OP ON OP.PAT_ENC_CSN_ID = E.PAT_ENC_CSN_ID
INNER JOIN CLARITY_EAP EAP  ON EAP.PROC_ID = OP.PROC_ID
INNER JOIN EDP_PROC_CAT_INFO EDP ON EDP.PROC_CAT_ID = EAP.PROC_CAT_ID
WHERE (EAP.PROC_NAME LIKE '%ECHO%' 
OR EDP.PROC_CAT_NAME LIKE 'ECH%')
AND EDP.PROC_CAT_NAME NOT IN ( 'CHG LABORATORY',    'LAB BLOOD ORDERABLES', 'PR GASTROENTEROLOGY',  'PR INJECTABLE MEDS',   'URINE ORDERABLES')

Doesn't Work

if object_id('tempdb..#ECHO_ORDERS') is not null  begin drop table #ECHO_ORDERS
SELECT DISTINCT
ENCOUNTER_ID        = OP.PAT_ENC_CSN_ID
,NOTE_TYPE          = OP.PROC_CODE
,CREATE_TIME        = OP.ORDER_INST
,OP.ORDER_PROC_ID
,EAP.PROC_NAME
INTO #ECHO_ORDERS
FROM PAT_ENC E 
INNER JOIN #SER SER ON SER.PROV_ID = E.VISIT_PROV_ID
INNER JOIN ORDER_PROC OP ON OP.PAT_ENC_CSN_ID = E.PAT_ENC_CSN_ID
INNER JOIN CLARITY_EAP EAP  ON EAP.PROC_ID = OP.PROC_ID
INNER JOIN EDP_PROC_CAT_INFO EDP ON EDP.PROC_CAT_ID = EAP.PROC_CAT_ID
WHERE (EAP.PROC_NAME LIKE '%ECHO%' 
OR EDP.PROC_CAT_NAME LIKE 'ECH%')
AND EDP.PROC_CAT_NAME NOT IN ( 'CHG LABORATORY',    'LAB BLOOD ORDERABLES', 'PR GASTROENTEROLOGY',  'PR INJECTABLE MEDS',   'URINE ORDERABLES')

I get this error message about the last line of code when I run the code that doesn't work

Msg 102, Level 15, State 1, Line 222
Incorrect syntax near ')'.

Answer 1

If you format your code properly, it's clear why your code doesn't work.

if object_id('tempdb..#ECHO_ORDERS') is not null  
begin 
    drop table #ECHO_ORDERS

    SELECT DISTINCT
        ENCOUNTER_ID        = OP.PAT_ENC_CSN_ID
        ,NOTE_TYPE          = OP.PROC_CODE
        ,CREATE_TIME        = OP.ORDER_INST
        ,OP.ORDER_PROC_ID
        ,EAP.PROC_NAME
    INTO #ECHO_ORDERS
    FROM PAT_ENC E 
    INNER JOIN #SER SER ON SER.PROV_ID = E.VISIT_PROV_ID
    INNER JOIN ORDER_PROC OP ON OP.PAT_ENC_CSN_ID = E.PAT_ENC_CSN_ID
    INNER JOIN CLARITY_EAP EAP  ON EAP.PROC_ID = OP.PROC_ID
    INNER JOIN EDP_PROC_CAT_INFO EDP ON EDP.PROC_CAT_ID = EAP.PROC_CAT_ID
    WHERE (EAP.PROC_NAME LIKE '%ECHO%' OR EDP.PROC_CAT_NAME LIKE 'ECH%')
        AND EDP.PROC_CAT_NAME NOT IN ( 
            'CHG LABORATORY',    'LAB BLOOD ORDERABLES', 'PR GASTROENTEROLOGY',  'PR INJECTABLE MEDS',   'URINE ORDERABLES'
        )

You have BEGIN and no END.