bsp; 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
/* * ready ... */ return (0) end go grant execute on sp_fix_idgap_invoices to public go
/* * end of file */
jazy 回复于：2002-12-25 18:23:17
Good !It 's very good! I think if you read this article throughly,though you don'y want to use identity characteristic of sybase ,you can learn some useful from it too!
!= NULL
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
