nbsp;highest existing key value
*/
select @gap_max = max(invoice_nr)
from invoices
if @@rowcount = 0 or @@error != 0
begin
print "Error occurred"
return (-1)
end
/*
* calculate new value for identity column in key table
*/
if @p_gap_low > 0
begin
/*
* now update the application table to correct
* the undesirable high key values
*/
update invoices
set invoice_nr = invoice_nr - @gap_size + 1
where invoice_nr > @gap_low
select @error = @@error, @rows = @@rowcount
if @error != 0 or @rows = 0
begin
print "No rows updated or error occurred"
return (-1)
end
/*
* tell user what happened
*/
print "%1! invoice numbers have been corrected.", @rows
print "Invoice numbers starting from %1! have been reset to %2! and further.",
@gap_high, @gap_low_plus_1
/*
* value to reset keytable with
*/
select @set_new_idval = @gap_max - @gap_size + 1
end
else
begin
/*
* case where keytable will be reset anyway
*/
select @set_new_idval = @gap_max
end
/*
* drop existing keytable; note that it's not necessary to
* unpartition the table first (like in 11.0), although the
* ASE documentation still says you can't drop a partitioned
* table...
*/
if object_id("invoices_keytable") != NULL
begin
-- commented out: this statement is not needed
-- alter table invoices_keytable unpartition
--
drop table invoices_keytable
end
/*
* now recreate the keytable. This can't be done in
* the same procedure as where the table is dropped, so
* this is done in a separate procedure
*/
exec sp_create_invoices_keytable
/*
* switch identity_insert on for this table
*/
set identity_insert invoices_keytable on
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
