爱莎居

.Net

导航

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:

 

Code

 

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

posted on 2008-08-12 23:04  焰凌  阅读(174)  评论(0编辑  收藏  举报