CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-proceduresalter-table
GLP
GLP

Reputation: 3675

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.

Upvotes: 9

Views: 55480

Answers (2)

Matt
Matt

Reputation: 15

I did it like this, then you don't need to worry about SQL injection:

ALTER PROCEDURE Add_DB_Field

(@FieldName varchar(100))

AS

ALTER TABLE Readings ADD tmpCol VARCHAR(100) NULL

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

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

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

Upvotes: 15

Related Questions