Geek asks and answers

Adding a column to a stored procedure

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

...

alter table tb1
add col1 varchar(1) null

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

I got an error message

col1 is not valid.

+5
source share
2 answers

Try creating a table with a default value of "Y" instead of inserting values ​​after that.

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

You will need GObetween two rows to create the table according to the documentation GO:

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.

GO .

EDIT

EXEC :

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

, SQL:

ALTER PROCEDURE Add_DB_Field

(@FieldName varchar(100))

AS

ALTER TABLE Readings ADD tmpCol VARCHAR(100) NULL

EXEC sp_RENAME 'Readings.tmpCol' , @FieldName, 'COLUMN'
0

More articles:


All Articles