Search code examples
sql-serversql-server-2008t-sqlstored-proceduresalter-table

adding a column in stored procedure

Can I have one single stored procedure to add a new column to a table and work on the column afterwords? For example, I have following stored procedure:

...

alter table tb1
add col1 varchar(1) null

insert into tb1(col1)
values ('Y')

I got an error saying

col1 is invalid.

Solution

  • Try creating the table with a default value of 'Y' instead of inserting the values afterwards. 

    alter table tb1 add col1 varchar(1) not null DEFAULT ('Y')

    You would need GO in between the two lines to have the table created, as per the GO documentation:

    SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.

    However, you can't have GO statements in stored procedures.

    EDIT

    Alternately, you could use the EXEC statement to execute your code:

    EXEC ('alter table tb1 add col1 varchar(1) null')
EXEC ('update tb1 set col1 = ''Y''')