|
; print "This is not a real identity gap"
return (-1)
end
end /* if @p_gap_low > 0 */
/*
* 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]
[办公软件]excel中的VBA中的With语句的使用介绍及实例 [系统软件]OLE with the internet explorer
[Delphi程序]override deal with window closing in database … [Delphi程序]Building ActiveX Controls with Delphi 3
[VB.NET程序]Socket Programming with VB [VB.NET程序]Managing Windows with WMI
[Web开发]ASP.NET with C#使用md5，sha1加密初探(10月21日首… [Web开发]XML with asp.net(1)
[Web开发]Send Email and Attachments with ASP.Net... [Web开发]Chapter 7. Working with ASP.NET and VB .NET
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
