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
insert invoices_keytable (dummy_key)
values (@set_new_idval)
set identity_insert invoices_keytable off
/*
* ready ...
*/
return (0)
end
go
grant execute on sp_fix_idgap_invoices to public
go
/*
* end of file
*/
这种资料不可多得哦！ 故保存下来，大家看看，很有益的！！（jazy）
上一页 [1] [2] [3] [4] [5] [6] |
