首页蓝耳朵|小小蓝耳朵广州图书馆外文室英语儿童读物资源介绍网佛教青年之友旧版收集永硕E盘Phonics Short Vowels Game 
带分隔符的字符串和表变量之间的转换(多用于一对多表结构里存储过程传递变量)
所属栏目:MS SQL 2008  时间:2011-12-20 15:53  作者:狼子

把一对多的几个记录根据条件取出用一个字符串返回,这个以前记录过:http://www.nnllok.cn/myBlog/archives/2007/3511.html,这里用的是一样子的方法

 
把带分隔符的字符串转成表变量,这个我用的是把分隔符替换成union的方法,比如对应字符串
'汽车,飞机,交通工具'
 
我替换成:
select '汽车' union select '飞机' union select '交通工具'
 
下面是完整的测试代码:
 
declare @NameList varchar(200)
 
--设置测试数据用的表
declare @DicTable table (DicId int, DicName varchar(20))
insert into @DicTable (DicId, DicName) values (1, '火车')
insert into @DicTable (DicId, DicName) values (1, '汽车')
insert into @DicTable (DicId, DicName) values (1, '飞机')
insert into @DicTable (DicId, DicName) values (1, '轮船')
insert into @DicTable (DicId, DicName) values (1, '单车')
 
--把测试表中的所有数据,以字符串返回
set @NameList = ''
select @NameList = @NameList + isnull(DicName, '') + ',' from @DicTable
if @NameList is null or @NameList = ''
   select ''
else
   select left(@NameList, len(@NameList) - 1) as AllDicItem
 
--设置测试用的字符串
set @NameList = '汽车,飞机,交通工具'
 
--把字符串转成表变量
declare @Sql varchar(1000)
set @Sql = 'select ''' + replace(@NameList, ',', ''' union select ''') + ''''
print @Sql
declare @NameListTable table (NowName varchar(50))
insert into @NameListTable (NowName) exec (@Sql)
 
--取测试表中原来就有数据
select DicId, DicName from @DicTable where CHARINDEX(DicName, @NameList) > 0
 
--取测试表中没有的数据
select NowName from @NameListTable K left join @DicTable D on K.NowName = D.DicName where D.DicId is null
 
----------------------------------------------
 
昨天转成表的方法,我用这样子的方法,是因为不想循环,就是,如果我要把这种方法写成存储过程或者函数,给别的存储过程调用,问题就出来了。。。
 
1.写成存储过程的时候,insert exec会报:INSERT EXEC 语句不能嵌套
 
2.写成表值函数的时候,insert exec会报:在函数内对带副作用的运算符 'INSERT EXEC' 的使用无效
 
所以我最后还是用回了循环分割。。。
 
create function dbo.GetTableFromString (@Txt varchar(2000))
returns @Table TABLE(ColValue varchar(50))
as
/*
NNLL 20111221 1047 把字符串转成结果集返回,字符串的每一项用,分割
*/
begin
 
 declare @SplitCharLen int, @SplitChar varchar(10), @Index int
 set @SplitChar = ','
 
 set @SplitCharLen = len(@SplitChar)
 
 while CHARINDEX(@SplitChar, @Txt)>0
  begin
   set @Index = CHARINDEX(@SplitChar, @Txt)
   insert @Table values (LEFT(@Txt, @Index - 1))
   set @Txt = SUBSTRING(@Txt, @index + @SplitCharLen, len(@Txt) - @index)
  end
 --插入最后一个记录
 insert @Table values(@Txt)
 
 --把关键字前后去空格
 update @Table set ColValue = ltrim(rtrim(ColValue))
 
 --把空记录删除
 delete from @Table where ColValue is null or ColValue = ''
 
 return
end
 
小楼宝宝的涂鸦花花(Imitater)的博客起名称骨测字皖ICP备06000023号-17