Hey all,

Perhaps I'm missing something, or I misunderstand deferred name resolution.

I have a stored procedure similar to below:

CREATE PROC sample
AS
/* this portion runs fine */
SELECT col_1, col_2
INTO new_table
FROM other_table


/* this part fails with errors :

invalid column name col_added_2
invalid column name col_added_1
*/
ALTER TABLE new_table ADD
col_added_1 BIT NULL,
col_added_2 CHAR(2) NULL

UPDATE new_table
SET col_added_2 = t.value, col_added_1 = t.column
FROM new_table n LEFT JOIN third_table t
ON n.col_1 = t.pk_col

The above runs fine as a script, or seperated into three sp's. Wrapping the
ALTER TABLE statement into transaction and committing it prior to the UPDATE
statement didn't help. I think it has something to do with the ALTER TABLE
statement forcing the sp to recompile, thus the errors with the UPDATE
statement.

Is this just a common-sense no-no for stored procedures? Or am I missing
something with the code?

TIA,

JasonL