/**//* function name:decStoreCode
purporse:分解以逗號分開的StoreCode字符串
author:seven
create date:2004/11/20
modifer:seven
modified date:2004/8/21
use exam:
*/ CREATEFUNCTION decStoreCode
(
@StoreCode nvarchar(255)--傳入BillNo字符串 )
RETURNS @TabStoreCode Table(StoreCode nvarchar(20))
AS BEGIN declare @strCurSub varchar(60)--當前的截取部分 declare @strBehindSub varchar(255)--後面的截取部分 declare @intCurPost int--當前的位置 declare @intLefLen int--剩下部分的長度 set @intCurPost =charindex(',',@StoreCode)--得到分號的位置 if(@intCurPost>0)
begin set @strCurSub=substring(@StoreCode,1,@intCurPost-1);
set @intLefLen=len(@StoreCode)-@intCurPost
set @strBehindSub=substring(@StoreCode,@intCurPost+1,@intLefLen)
end else begin set @strCurSub=@StoreCode
end insert @TabStoreCode values(@strCurSub)--分解到table中 while @intCurPost>0 begin set @intCurPost=charindex(',',@strBehindSub)
if @intCurPost!=0 begin set @intLefLen=@intLefLen-@intCurPost
set @strCurSub=substring(@strBehindSub,1,@intCurPost-1)
set @strBehindSub=substring(@strBehindSub,@intCurPost+1,@intLefLen)
end else begin set @strCurSub=@strBehindSub
end insert @TabStoreCode values(@strCurSub)--分解到table中 end return--返回资料集合 END