t execute on sp_insert_invoice to public
go
/*
* Create the procedure to fix an identity gap.
* It takes one parameter, and can be called in two ways:
*
* 1. If "-1" is specified as a parameter, the keytable
* will be dropped and recreated according to the highest
* existing invoice number. The best use of this form
* is to always run this procedure directly after server
* startup, so that a possible identity gap is be corrected
* before new invoices are inserted which could cause problems.
*
* 2. If another value is specified, it must be invoice number
* generated just before the identity gap occurred. All invoices
* having a higher invoice numbers than this will be corrected
* downwards to create a consecutive range. The keytable will
* be recreated and adjusted accordingly.
* Example: suppose the following invoice numbers exist:
* (...)
* 10028
* 10029
* 10030
* 10031 <--\
* 5000002 <--- identity gap here
* 5000003
*
* Now run "exec sp_fix_idgap_invoices 10031", and the last two
* 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
*/
&n上一页 [1] [2] [3] [4] [5] [6] 下一页 |
