Alter Table and Update column in single stored procedure?

user_6205382 0
Viewed 2466

I'm am trying to create a stored procedure that adds a new column then sets the column to a value:

CREATE PROCEDURE alter_then_update 
AS 
    ALTER TABLE table_1 
        ADD bundle_type NVARCHAR(10);

    UPDATE table_1 
    SET bundle_type = 'Small'

I keep getting an error that I have an invalid column name of bundle_type. I get that the column isn't created yet. Can't I store this in a stored procedure and have the code execute line by line? Doesn't the semi-colon execute in order? I tried using GO but the query started to execute instead.

How can I put both of these statements into one procedure?

stored-procedures sql-server sql
1 Answers

The problem is that the procedure is compiled before it is executed. I think the only way around this in a stored procedure is dynamic SQL:

CREATE PROCEDURE alter_then_update AS 
BEGIN
    ALTER TABLE table_1 ADD bundle_type NVARCHAR(10);

    EXEC sp_executesql 'UPDATE table_1 SET bundle_type = ''Small''';
END;
user_11440350
Related