sql server - C# MSSQL alter table then modify values -

while following work fine in sql server management studio, won't work in c#:

declare @periodid bigint; select top 1 @periodid = periodid periods order periodid desc; if not exists(select * information_schema.columns column_name = n'periodid' , table_name = n'mobileplans') begin     begin     alter table mobileplans add periodid bigint null     end     begin     update mobileplans set periodid = @periodid     end     begin     alter table mobileplans alter column periodid bigint not null     end end 

in c#, keeps telling me invalid column name 'periodid'. , after spending couple of hours searching, thought i'd ask here.

while searching, came across http://bytes.com/topic/c-sharp/answers/258520-problem-sqlcommand-t-sql-transaction, couldn't translate conditional query that.

why can't c# same management studio?

is there way query does, works in c#? need perform on 400 databases, i'd script me.

thanks in advance!

sql server version 2008. manager version 2008 (10.0.2531). .net framework version 2.0.

i "invalid column name 'periodid'" running in management studio, if table doesn't have periodid column.

repro:

create table periods (     periodid bigint not null ) go insert periods(periodid) select 1 go create table mobileplans (     blah int not null ) go insert mobileplans(blah) select 2 go declare @periodid bigint; select top 1 @periodid = periodid periods order periodid desc; if not exists(select * information_schema.columns column_name = n'periodid' , table_name = n'mobileplans') begin     begin     alter table mobileplans add periodid bigint null     end     begin     update mobileplans set periodid = @periodid     end     begin     alter table mobileplans alter column periodid bigint not null     end end 

the reason simple - sql server tries compile each batch completely. if column exists, update statement can compiled. if not, error.

if put update inside exec:

exec('update mobileplans set periodid = ' + @periodid) 

then compiled @ point column exist.