我有一函数: CREATE function GetNames(@pid varchar)
returns varchar(8000)
as
begin
declare @names varchar(1000)
set @names = ''
select @names = @names + ',' + tag from all_adv_cls where id in (@pid)
set @names = stuff(@names ,1,1,'')
return @names
end@pid 传进去是 1,2,3 为什么出来的 @names只有一个值,没有id对应的name1,name2,name3 ??帮助解决一下,先谢!
returns varchar(8000)
as
begin
declare @names varchar(1000)
set @names = ''
select @names = @names + ',' + tag from all_adv_cls where id in (@pid)
set @names = stuff(@names ,1,1,'')
return @names
end@pid 传进去是 1,2,3 为什么出来的 @names只有一个值,没有id对应的name1,name2,name3 ??帮助解决一下,先谢!
returns varchar(8000)
as
begin
declare @names varchar(1000)
set @names = ''
select @names = @names + ',' + tag from all_adv_cls where charindex(','+id+',',','+@pid+',')>0
set @names = stuff(@names ,1,1,'')return @names
end
returns varchar(8000)
as
begin
declare @names varchar(1000)
set @names = ''
select @names = @names + ',' + tag from all_adv_cls where ','+@pid+',' like '%,'+id+',%'
set @names = stuff(@names ,1,1,'')return @names
end
id name
1 a1
2 a2
3 a3SELECT dbo.GetNames('1,2,3') AS Expr1
FROM all_advs 应该返回 a1,a2,a3 呀
要么用
charindex判断
要么写成动态的
如
select @sql=...+ 'id in (' +@pid + ')'不然会把 @pid 看做一个整体...
patindex()
都行
这个有问题
表all_adv_cls
id tag
1 a1
2 a2
3 a3就这三个,也应该能列出 a1,a2,a3 呀
---------
@pid只能是一个字符
CREATE function GetNames(@pid varchar(8000))
---------
就可以啦.
returns varchar(8000)
as
begin
declare @curState int
declare @curKey varchar(50)
declare @count int
declare @name varchar(50)
declare @names varchar(8000)
set @curState=1
set @curKey = ''
set @GID=@GID+','
set @names=''
while charindex(',',@GID,@curState) > 0
begin
set @count=charindex(',',@GID,@curState)
set @count=@count-@curState
Set @curKey = substring(@GID,@curState,@count)
select @name=name from all_adv_cls where pid=@curKey
set @names=@names+','+@name
set @curState = charindex(',',@GID,@curState) + 1
set @curKey = ''
end
return @names
end
执行:select dbo.getname('1,2,3')
id是什么类型的?
如果是int类型就有问题,并且用id in (@pid)也不行,会把@pid作为一个整体来处理,亦即@pid是一个值,而不是几个值的集合.