/* * 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
!= NULL
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
