Approach To Creating A Procedure That Contains Dll , Dml To Upgrade A System

Hi.

I am between beginner and intermediate level of knowledge of sql server.
I am dealing with an sql server 2005 situation (oracle also but thats another
thread/story).

I need to assemble a process that an end user can use easily to update a
database. The update consists of some column lengthening, checks to make sure
new data will fit in columns after the ddl is executed, and then generating
new data (changing primary key column data and keeping all the refering
objects/rows in sync). I also don't want to have an error leave things in a
bad state.

I'm wondering if there is anything special to look out for, and if there are
any suggestions on approach. Here is my current approach:


write a single stored procedure to do all the work.
have it check for existence of table01_bak and table02_bak
delete them if they exist
recreate them

copy table01 and table02 to table01_bak and table02_bak
calculate the new primary key values needed
make sure they will fit (I need to bail out and tell the user to make an
adjustment at this point if they will not)

start a transaction

generate the new primarky key values and insert them
update all the other tables that refer to the still existing primary keys to
point them to the newly generated ones
delete all the old rows using the original primary keys

commit a transaction

delete the table01_bak and table02_bak



thanks
Jeff

Jeff Kish