Mike,
The table is being altered in the same batch as the update statements. When
the code is parsed, it fails to parse correctly because the column is
missing. Therefore the entire batch fails.
If run as a script, you could do the following.
if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS where Table_Name =
'JVClaim'and Column_Name = 'ClaimType')
alter table JVClaim add ClaimType varchar(40);
GO
update JVClaim Set ClaimType = 'Quality Claim' Where QualClaim = 1;
update JVClaim Set ClaimType = 'Quantity Claim-Full Carton' Where
QuantClaim = 1;
GO
The GO in the Query window or through OSQL, ISQL, SQLCMD, etc. is the batch
execution command. So this will parse the first batch, then execute it,
then parse the second batch, then execute it. It is not quite equivalent to
what you did, since the second batch will run whether the alter happened or
not.
RLF
[quoted text, click to view] "GeyikBaba" <mike@ease.com> wrote in message
news:uGBlfia6HHA.5360@TK2MSFTNGP03.phx.gbl...
> Using SQL Server 2003, I am trying to add a field, then populate it. I am
> getting an error on the last two lines saying the column ClaimType does
> not exist. When I look at the columns in the table, indeed, it is not
> there.
>
> If I remove the two update lines and run the query again, the cilumn
> ClaimType is added correctly. THe problem has to be simple, but I don't
> see it.
>
> Thanks
>
> Mike Thomas
>
>
>
> if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS where Table_Name =
> 'JVClaim'and Column_Name = 'ClaimType')
>
> BEGIN
>
> alter table JVClaim add ClaimType varchar(40);
>
> update JVClaim Set ClaimType = 'Quality Claim' Where QualClaim = 1;
>
> update JVClaim Set ClaimType = 'Quantity Claim-Full Carton' Where
> QuantClaim = 1;
>
> END
>
>
