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