Poor code: Dynamic changing of table schema

When you write SQL Scripts or Stored Procedures. Please keep in mind that don't change the schema of table with update data for the table together.

 

Problem repro steps:

1: prepare following scripts:

 

 SELECT FieldA=0, FieldB=1 INTO TestTable
 ALTER TABLE TestTable ADD FieldC INT
 UPDATE TestTable SET FieldC=3
 SELECT * FROM TestTable
 DROP TABLE TestTable

 

2: Excute line 1. Then the DB has a new table named TestTable with two columns FieldA and FieldB.

3: Excute line 2 and line 3 together. The error occured. Invalid column name 'FieldC'.

 

Explain problem:

Theoretically line 2 and line 3 are correct. add a new column and then update the value of this new column. When you execute these two lines separately, the result is success. But it will cause the error if you execute them together.

 

Solution:

1: Don't change the schema with data update. Separate them to two different part. Such as two different stored procedure.

2: Make your schema stable. Change the line 1 to SELECT FieldA=0, FieldB=1, Fieldc=NULL INTO TestTable