Andrew,

You can only do this in Dynamic SQL, in other words, you would need to construct your whole UPDATE command as a string and execute it dynamically with something like

set @SQLString = N'ALTER table AdultInfo add MaritalStatus char(1) null' 
	EXECUTE sp_executesql @SQLString

Are you sure you need to do this? May be you can put the whole UPDATE command without WHILE loop?


>Hi All,
>
>I'm new to T-SQL coming from a VFP background. I have a table with a number of columns State1, State2, State3 etc that I want to update via an "UPDATE RESULTS SET Statenn = (select SUM(MTDSALES) FROM SALES where STATE = @lnState)". Is it possible for me to refer to the column name programmatically so that I can run the Update state inside a WHILE loop ?
>
>ie something like
>
>SET @lnState = 1
>WHILE @lnState < 10
> lcState = 'STATE' + CAST(@lnState AS CHAR)
> BEGIN
> UPDATE RESULTS
> SET &lcState = (select SUM(MTDSALES) FROM SALES where STATE = @lnState)
> SET @lnState = @lnState + 1
> END
>
>
>Tia,
>
>Andy