--產生傳票號碼 @CpNoDate:傳票日期(8位) @JmpNo:為要跳過的傳票號碼(流水號) @CpNo:輸出(流水號)
create procedure prc_GetCpNo (@CpNoDate char(8),@JmpNo int,@CpNo int output) with ENCRYPTION
as
declare @d33no char(12),@tmpI int
set nocount on
declare Cp_cursor cursor for Select distinct D33NO from Dt3_3 Where [email protected] order by D33NO for read only
open Cp_cursor
fetch next from Cp_cursor into @d33no
if @@fetch_status=-1
begin
set @CpNo=0
if @[email protected] set @[email protected]
end
else
begin
set @tmpi=0
while @tmpi <=1
begin
if right(@d33no,4) <> '0000 ' and @tmpi=0
set @cpno=0
else
begin
if @cpno is null
begin
set @cpno=convert(int,right(@d33no,4))
fetch next from Cp_cursor into @d33no
end
while @@fetch_status=0
begin
if convert(int,right(@d33no,4))[email protected]> 1 if @JmpNo <> @cpno+1 break
set @cpno=convert(int,right(@d33no,4))
fetch next from Cp_cursor into @d33no
end
set @[email protected]+1
end
if @jmpno <> @cpno
set @tmpi=1
else
if @@fetch_status=-1
begin
set @[email protected]+1
set @tmpi=1
end
set @[email protected]+1
end--while @tmpi結束
end
close Cp_cursor
deallocate Cp_cursor
set nocount off
此過程在何時就在已查到的d33no上加1?何是就返回原來的d33no?
------解决方案--------------------
[email protected]，[email protected]
另外，感覺這個寫複雜了，生成流水號應該不至於要寫的這麼複雜的。