* invoices will be corrected. Also, the next invoice will
* receive number 10034.
*/
if object_id("sp_fix_idgap_invoices") != NULL
begin
drop procedure sp_fix_idgap_invoices
end
go
create procedure sp_fix_idgap_invoices
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
@p_gap_low numeric(10,0)
as
begin
declare @gap_low numeric(10,0)
declare @gap_low_plus_1 numeric(10,0)
declare @gap_high numeric(10,0)
declare @gap_size numeric(10,0)
declare @gap_max numeric(10,0)
declare @set_new_idval numeric(10,0)
declare @rows int
declare @error int
if (@p_gap_low != -1) and (@p_gap_low < 1)
begin
print "Invalid invoice number specified."
print "You must specify the last correct invoice number before the identity gap."
return (-1)
end
/*
* First check identity_insert is not active for any
* other table: there can be only one at a time
* if another table is already in this mode, the
* procedure will be aborted here and user must switch off
* identity_insert on that other table before continuing.
*/
set identity_insert invoices_keytable on
set identity_insert invoices_keytable off
if @p_gap_low > 0
begin
/*
* check the row at lower end of gap exists
*/
select @gap_low = invoice_nr
from invoices
where invoice_nr = @p_gap_low
select @error = @@error, @rows = @@rowcount
if @error != 0
begin
print "Error occurred"
return (-1)
end
if @rows = 0
begin
print "Could not find invoice number specified."
print "You must specify the last correct invoice number before the identity gap."
return (-1)
end
select @gap_low_plus_1 = @gap_low + 1
/*
* determine the size of the identity gap
*/
select @gap_high = min(invoice_nr)
from invoices
where invoice_nr > @gap_low
if @@rowcount = 0 or @@error != 0
begin
print "Row at upper end not found or error occurred"
return (-1)
end
/*
* calculate size of gap; must be > 99 to be
* considered a real identity gap(this is
* an arbitrary limit). Small 'gaplets' of
* non-existing numbers can always exists due to
* rolled-back transactions
*/
select @gap_size = @gap_high - @gap_low
if @gap_size < 100
begin
print "This is not a real identity gap"
return (-1)
end
end /* if @p_gap_low > 0 */
/*
* determine the 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 th
