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
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
*/
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!
but ,I don't like to use identity !
上一页 [1] [2] [3] [4] [5] [6] |
