--拆分字符卡
create FUNCTION s(@id varchar(20),@编号串 varchar(max))
RETURNS @tab table(ID varchar(20),编号 varchar(30))
begin
declare @ipos int, @bh varchar(20);
while 1>0
begin
set @ipos=Charindex(',',@编号串,1)
if @ipos>0
set @bh=substring(@编号串,1,@ipos-1)
else
set @bh=@编号串
set @编号串=substring(@编号串,@ipos+1,len(@编号串))
if LEN(@bh)>0
begin
insert into @tab(ID,编号)values(@id,@bh);
end
if isnull(@ipos,0)<1 break
end
return
end
--循环获取数据(存储过程版本)
alter proc c
as
begin
create table #w
(
id varchar(20),
name varchar(20)
)
declare @编号 varchar(max)
declare @id varchar(max)
select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* into #T from ss
declare @i int;
set @i=1;
while(@i<=(select count(*) from ss))
begin
set @编号=(select name from #T where RowNumber=@i)
set @id=(select id from #T where RowNumber=@i)
insert into #w select * from s(@id,@编号)
set @i=@i+1
end
select * from #w
drop table #T
drop table #w
end
--exec c
--循环获取数据(函数版本) www.2cto.com
alter FUNCTION s1()
RETURNS @tab table(ID varchar(20),编号 varchar(30))
begin
begin
declare @编号 varchar(max)
declare @id varchar(max)
declare @i int;
set @i=1;
while(@i<=(select count(*) from ss))
begin
set @编号=(
select name from
(
select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* from ss
)a where a.RowNumber=@i
)
set @id=(
select id from
(
select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* from ss
)b
where b.rowNumber=@i
)
insert into @tab select * from s(@id,@编号)
set @i=@i+1
end
end
return
end
--select * from s1()
ss表结构如下
ID,name两列
摘自 事在人为的专栏