|
|
Posted by Jeff Kish on 05/15/07 22:59
On Tue, 15 May 2007 21:47:09 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:
>Jeff Kish (jeff.kish@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
Navigation:
[Reply to this message]
|