/*
   * 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 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
*/

上一页  [1] [2]