Add a column and update it in same stored procedure in SQL Server 2008

Question

if I have a stored procedure say

CREATE PROCURE w AS

ALTER TABLE t ADD x char(1)

UPDATE t set x =1

Even when it lets me create that stored procedure (if I create it when x exists), when it runs, there is an error on the UPDATE statement because column x doesn't exist.

What's the conventional way to deal with this, it must come up all the time? I can work around it by putting the UPDATE inside EXEC, is there another/better way?

Thanks

Answer 1

Rather than adding a column like this and then updating its value, you can add a column with a default value

CREATE PROCEDURE w AS 

ALTER TABLE t ADD x char(1) NOT NULL CONSTRAINT abc DEFAULT 1

Answer 2

ALTER TABLE in the context of 1st TRANSACTION and UPDATE in the context of 2nd:

CREATE PROCEDURE w
AS
   BEGIN TRAN
      ALTER TABLE ..
   COMMIT

   BEGIN TRAN
      UPDATE ..
   COMMIT
END