/****** Object: UserDefinedFunction [dbo].[F_GetBarcode] Creator bing Script Date: 05/17/2011 14:09:35 ******/

CREATE function [dbo].[F_GetBarcode](@StartBarcode nvarchar(50),@EndBarcode nvarchar(50))
returns @retFindReports TABLE(barcode nvarchar(50))
as
begin
if len(@StartBarcode)=len(@EndBarcode)
begin
declare @str nvarchar(100),@Barcode nvarchar(50),@BarcodeLen int,@i int,@j int,@t nvarchar(10),@r nvarchar(50)
set @str='0123456789ABCDEFGHJKLMNPQRSTUVWXYZ'
set @BarcodeLen=len(rtrim(@StartBarcode))
set @t=''
insert into @retFindReports select @StartBarcode
while @StartBarcode<@EndBarcode
begin
set @i=1
lab:
set @j=1

while @j<=34
begin
if substring(@str,@j,1)=substring(@StartBarcode,@BarcodeLen+1-@i,1)
begin
if @j<34
begin
set @t=substring(@str,@j+1,1)
end
else
begin
set @t='0'
end
break
end
set @j=@j+1
end

set @StartBarcode=substring(@StartBarcode,1,@BarcodeLen-@i)+@t+substring(@StartBarcode,@BarcodeLen-@i+2,@i+1)

if @t='0'
begin
set @i=@i+1
goto lab
end
insert into @retFindReports select @StartBarcode
end
end
else
begin
insert into @retFindReports select N'字串长度不一样'
end
return
end