I am trying to add a column to a table, and then update that newly added column.  these two things are relatively simple, however when I place them inside an 'if' statement, i get the error:
Invalid column name 'TOTAL_VOTES'

Here is my SQL:

if (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'RESULTS'
 AND COLUMN_NAME = 'TOTAL_VOTES'
   ) = 0

begin
ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0); --WITH VALUES

 UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10)

end

i need the if check b/c I only want to add and populate the column if it doesn't already exist in the table. (prior to this I check to see if the table exists)

I have tried to set a variable and then based off of that variable, populate the new column, but that doesn't work either.

any suggestions?  Why does the if() begin/end seem to cause errors with the alter/update statements?

How can I get around this?

Thanks

Are you sure you are referencing the same table with the same owner?

Try updating the table specifying the owner

UPDATE dbo.RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10)

Hey Rodrigo-

I am referencing the same table, I changed my t-sql statement and received the same error:
Invalid column name 'TOTAL_VOTES'

any other ideas?

thanks!

Hello,

Try with GO before UPDATE:

IF(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'mytable'
 AND COLUMN_NAME = 'mycol'
   ) = 0

ALTER TABLE mytable ADD mycol int not NULL DEFAULT (0)
GO

UPDATE mytable SET mycol = myval

Liliana.

Hey Liliana-

Thanks for suggestion.  I tried that, and T-SQL complains about syntax.  I think that 'GO' basicly executes all the script prior to it.  So because I have the Alter table, and Update statements inside of 'begin/end', the syntax gets funny in that the 'begin' has no 'end'.  I need the 'begin/end' b/c if the column does not exist on the table, i don't want to issue the update statement.

The solution I have come up with is the following:

if ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'RESULTS'
 AND COLUMN_NAME = 'TOTAL_VOTES'
   ) = 0
BEGIN
 exec('ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0);');
 exec('UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10);');
end

I am bascily forcing the T-SQL to execute by using the exec() command.

Thanks for everyone's help! 

Leme know if you have a better way to solve this!

Thanks

Try something like this:

if exists(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'RESULTS'
 AND COLUMN_NAME = 'TOTAL_VOTES')
BEGIN

        SELECT 0

END

ELSE

BEGIN
 exec('ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0);')

 exec('UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10);');
END