• I don't get any syntax errors.  That error must be because of some text/code before or after the main code.

    But you will likely get exec errors, since the column won't exist in the table at the time SQL parses the proc.

    In these types of situations, I often use dynamic SQL instead:


    --Remove providers that are not on the CPC+ Accepted Physician List (Oct 2017)
    delete from Mem_BCBSMCom where NPI not in (Select NPI from CMS_Accepted_Providers)

    --Add column Program column to table
    exec('alter table [Mem_BCBSMCom] add [Program] char(8);')
    exec('update [Mem_BCBSMCom] set [Program] = ''BCBSMCom'';')

    --Add column Program column to table
    exec('alter table [Mem_BCBSMMA] add [Program] char(7);')
    exec('update [Mem_BCBSMMA] set [Program] = ''BCBSMMA'';')

    --Add column Program column to table
    exec('alter table [Mem_BCN] add [Program] char(3);')
    exec('update [Mem_BCN] set [Program] = ''BCN'';')

    --etc.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.