Dynamically add column in procedure

Question

Post reply

Dynamically add column in procedure

devaraya

Default port

Points: 1455
More actions
December 10, 2008 at 6:12 am

#198821

Hi Everybody,
I am adding dynamically a column to a table in stored procedure. I am getting error 'Invalid column name' . In the procedure i am updating the column with values for that i am checking for the column. I am using database server Sql server 2005.
IF NOT EXISTS(SELECT NAME FROM SYS.COLUMNS WHERE NAME = N'OPTION'
AND OBJECT_ID = OBJECT_ID(N'MARKS_SHEET'))
BEGIN
SELECT @sql= 'ALTER TABLE MARKS_SHEET ADD OPTION VARCHAR(50)'
EXEC SP_EXECUTESQL @sql
END
Thank you very much
Ramaa

Viewing 5 posts - 1 through 4 (of 4 total)

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

The word OPTION is a keyword in sql server

Try using a different name

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Also, why use dynamic sql for this?

SELECT @sql= 'ALTER TABLE MARKS_SHEET ADD OPTION VARCHAR(50)'

EXEC SP_EXECUTESQL @sql

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

brainy SSCrazy Points: 2445 More actions · Answer 3

IF NOT EXISTS(SELECT NAME FROM SYS.COLUMNS WHERE NAME = N'OPTION'
AND OBJECT_ID = OBJECT_ID(N'MARKS_SHEET'))
BEGIN
SELECT @sql= 'ALTER TABLE MARKS_SHEET ADD OPTION VARCHAR(50)'
EXEC SP_EXECUTESQL @sql
END

Is it possible for you to paste the error u r getting. I think it would be helpful in resolving your problem.

devaraya Default port Points: 1455 More actions · Answer 4

Hi All,

Thank you for your response.

Hi Madivanan pls execuse me the column name is correct_option.

The error i am getting is Invalid column name 'correct_option'

After checking for the existence of the column, i am doing some update operation on the column. In the procedure, if not exists(select ... from syscolumns ) is not checking directly it is going to the update statement and giving error.

Ramaa