Invalid column error

Question

Post reply

Invalid column error

rwsIII

Old Hand

Points: 359
More actions
February 3, 2006 at 4:11 pm

#170330

Hey all-
I am trying to add a column to a table, and the update that newly added column. these two things are relatively simple, however when I place them inside a 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?
Thanks
Why does it throw this error, and how can I get around it?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>Why does it throw this error, and how can I get around it?

SQL is parsed, an execution plan is generated, then it is executed.

The parser is throwing the error because the column does not exist at parse time.

You have to do this as 2 separate SQL batches, or 1 batch with dynamic SQL for the UPDATE statement.

rwsIII Old Hand Points: 359 More actions · Answer 2

thanks for the help PW!

How do you do Dynamic SQL for the update statement? I've never done dynamic SQL...

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Here's one way to do it...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'RESULTS'
AND COLUMN_NAME = 'TOTAL_VOTES'
IF @@ROWCOUNT = 0
BEGIN
    ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0); 

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

**ASCII stupid question, get a stupid ANSI !!!**

rwsIII Old Hand Points: 359 More actions · Answer 4

Hey Sushila-

I tried your above method, but I get the same result...an error saying:

"Invalid column name 'TOTAL_VOTES'."

I don't understand what makes your method dynamic...can you explain?

thanks

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

>>How do you do Dynamic SQL for the update statement? I've never done dynamic SQL...

Declare @SQL varchar(1000)

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

--Construct SQL to executed dynamically

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

--Execute the SQL

exec (@SQL)

end

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 6

Something like this..

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] as Option1 + Option2

END

HTH

Mathew J Kulangara
sqladventures.blogspot.com

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Just to explain - my method wasn't supposed to be dynamic....

I was doing it in 2 separate batches...works for me...can't figure out why you continue getting the same error msg ?!

**ASCII stupid question, get a stupid ANSI !!!**

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

IF @@ROWCOUNT = 0

BEGIN

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

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

END

>>I was doing it in 2 separate batches...works for me...

How did you get 2 batches within the same BEGIN ... END block of an IF statement ?

rwsIII Old Hand Points: 359 More actions · Answer 9

Thanks for all the help!

I stumbled apon the exec() command prior to seeing your lasts couple posts PW, and it does exactly what I need! Much thanks!

I did it like this:

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

Like PW said, I am bascily forcing the T-SQL to execute by using the exec() command for each of my batches.

Thanks for everyone's help!

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

My bad...I shouldn't have said "batches"....was in a hurry...

BUT...when I do an "if count(*) = 0 etc..." I get the "columns doesn't exist" error - whereas when I send 2 separate sql statements it alters the table and updates the column just fine...I've done this about 5 times now and it works every single time...still trying to figure out why it's working for me...unless you have any explanations ?!?!?!?!

**ASCII stupid question, get a stupid ANSI !!!**