Alter column value directly after creation (within a single stored procedure)

Question

I need to add an additional column to an existing table, and afterwards insert the appropriate column values for the existing records. I need to do this within a single stored procedure.

So far I have the following (SQL Management studio reports that it cannot find the new column in order to add the values to the existing records):

    BEGIN
    ALTER TABLE tbl1
    ADD col_add int

    UPDATE tbl1
    SET [col_add]='value'
    WHERE col_pk = 1
    END

I have tried adding a "GO" keyword between the ALTER and UPDATE statements, but then it reports a syntax error.

Thanks in advance.

provençal le breton · Accepted Answer · 2013-04-26T12:42:29.817

2

I think You must commit your 'alter table' and then do your update, and don't forget to commit your update too :

 BEGIN
  BEGIN TRAN
    ALTER TABLE tbl1
    ADD col_add date
COMMIT;
   BEGIN TRAN
    UPDATE tbl1
    SET [col_add]='value'
    WHERE col_pk = 1
COMMIT;
    END

edited Apr 26 '13 at 12:42

answered Apr 26 '13 at 12:19

provençal le breton

1,428
4
26
43

This is half way there, but you need to begin your transactions with `BEGIN TRAN` before you can commit them – GarethD Apr 26 '13 at 12:25
Is that really needed if you make commit one after another? Maybe if you make 'alter', 'update' and then would commit, you have to add transaction, but I don't think if you make 'alter' 'commit' 'update' 'commit', you need transaction. maybe I'm wrong but... – provençal le breton Apr 26 '13 at 12:28
Yes it is needed, test it. The procedure will compile without `BEGIN TRAN`, but will come up with an error when run. There is a test script [here](http://sqlfiddle.com/#!3/0ed4a/1), which won't run because of permissions on SQL Fiddle, but you can try it on your DB. If you uncomment the `BEGIN TRAN` lines you will see the procedure runs with no error (if run once). – GarethD Apr 26 '13 at 12:38
Ok. I edited response with your comments. – provençal le breton Apr 26 '13 at 12:43

score 2 · Answer 2 · answered Apr 26 '13 at 12:20

The table name error occurs during parsing. In SSMS, go works because it separates batches. But a stored procedure is always a single batch.

You can use exec or sp_executesql to force a new round of parsing inside a stored procedure:

exec ('UPDATE tbl1 SET [col_add]='value' WHERE col_pk = 1')

Alter column value directly after creation (within a single stored procedure)

2 Answers2