approach to creating a procedure that contains dll , dml to upgrade a system

[Jeff Kish]

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

[Erland Sommarskog]

Jeff Kish (je*******@mro.com) writes:

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:

One thing to remember is that SQL Server will first compile the
procedure according to the old table definition. It will certainly
recompile as you perform the ALTER TABLE statements. But you may
face the situation that the procedure does not compile with the old
definitions. This happens when you add new columns, and you seem to
only be changing existing columns, so you may get away with it.

In case you run into to it, the solution is to put troublesome statements
in dynamic SQL, or possibly an inner stored procedure.

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)

If the new values don't fit, you should get an error provided that
ANSI_WARNINGS are on.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Kish]

On Tue, 15 May 2007 21:47:09 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:

>Jeff Kish (je*******@mro.com) writes:

>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:

One thing to remember is that SQL Server will first compile the
procedure according to the old table definition. It will certainly
recompile as you perform the ALTER TABLE statements. But you may
face the situation that the procedure does not compile with the old
definitions. This happens when you add new columns, and you seem to
only be changing existing columns, so you may get away with it.

In case you run into to it, the solution is to put troublesome statements
in dynamic SQL, or possibly an inner stored procedure.

>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)

If the new values don't fit, you should get an error provided that
ANSI_WARNINGS are on.

thanks much!
As problems arise, I'll investigate dynamic sql for a solution. I assume you
are talking about placing the alter table commands in dynamic sql then...
right?
Regards

Jeff Kish

[Erland Sommarskog]

Jeff Kish (je*******@mro.com) writes:

As problems arise, I'll investigate dynamic sql for a solution. I assume
you are talking about placing the alter table commands in dynamic sql
then... right?

No, rather it's the commands that follow that you may have to put in
dynamic SQL. But you will find out if you need to.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx