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.

Answers


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''')

Need Your Help

Maven including old SNAPSHOT when building

java osx maven snapshot

I have switched from the 13.4.20-SNAPSHOT to developing the 14.1.18-SNAPSHOT. With this change, I have altered some of the packages. Now instead of a commons path, they are using a libraries path.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.