需求如下:有一个函数中的有一个查询条件是需要做in操作的,条件通过参数传进来,例如传进的条件为: 'aa,bb,cc,dd,ee,ff '
需要通过in操作来进行查询,偶试了N种方法,都没成功.最后只好先把传进来的条件拆分,然后再循环查询处理.(特别说明:不希望建临时表处理),现在的处理方式如下:
CREATE function GetSimpleDic(@Value varchar(1000))
returns varchar(8000)
as
begin
declare @tmpStr varchar(300)
declare @Value1 varchar(1200)
declare @RetVal varchar(8000)
set @RetVal= ' '
Set @[email protected]
declare @myPos int
set @myPos=charindex( ', ',@Value1)
while @myPos> 0
begin
set @tmpStr= ' '
select @tmpStr=DictCode from SecSimpleDict
Where DictValue = substring(@Value1,0,@myPos)
set @[email protected]+isnull(@tmpStr, ' ')+ '; '
set @Value1=substring(@Value1,@myPos+1,len(@Value1)[email protected])
set @myPos=charindex( ', ',@Value1)
end
set @tmpStr= ' '
select @tmpStr=DictCode from SecSimpleDict
where [email protected]
set @[email protected]+isnull(@tmpStr, ' ')
return @RetVal
end
100分求哪位高手做成直接使用in查询的函数........
------解决方案--------------------
CREATE function GetSimpleDic(@Value varchar(1000))
returns varchar(8000)
as
begin
declare @tmpStr varchar(300)
declare @Value1 varchar(1200)
declare @RetVal varchar(8000)
set @RetVal= ' '
Set @[email protected]
declare @myPos int
set @myPos=charindex( ', ',@Value1)
while @myPos> 0
begin
set @tmpStr= ' '
select @tmpStr=DictCode from SecSimpleDict
Where DictValue = left(@Value1,@myPos)
set @[email protected]+isnull(@tmpStr, ' ')+ '; '
set @Value1=stuff(@Value1,1,@myPos+1, ' ')
set @myPos=charindex( ', ',@Value1)
end
set @tmpStr= ' '
select @tmpStr=DictCode from SecSimpleDict
where [email protected]
set @[email protected]+isnull(@tmpStr, ' ')
return @RetVal
end
------解决方案--------------------
CREATE function GetSimpleDic(@Value varchar(1000))
returns varchar(8000)
as
begin
declare @RetVal varchar(8000)
set @RetVal= ' '
set @Value = ', '[email protected]+ ', '
select
@[email protected]+isnull(DictCode, ' ')+ '; '
from
SecSimpleDict
where
charindex( ', '+rtrim(DictValue)+ ', ',@Value)> 0
set @RetVal=left(@RetVal,len(RetVal)-1)