Menu
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.
639
To add a column, click Add, click the field or repeating group for which you want to create a column, and then type a name for the column in the Column name box.
We can use the ALTER TABLE statement to alter our existing table and add in this new column. The basic syntax for adding a new column is as follows: ALTER TABLE table_name ADD column_name data_type constraints; The SQL ALTER TABLE add column statement we have written above takes four arguments.
To insert a single column: Right-click the whole column to the right of where you want to add the new column, and then select Insert Columns. To insert multiple columns: Select the same number of columns to the right of where you want to add new ones. Right-click the selection, and then select Insert Columns.
If we want to add multiple columns to the existing table using any single statement, we can use the below syntax: ALTER TABLE table_name (Name of the table) ADD [COLUMN] column_definition, (for adding column)
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''')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
