-- 一个字段里面的值是 RoleInfo表的Id '1,2,3,4,5' 如何根据 该字段的值查询到 RoleInfo 的名称
select stuff( (select ','+RloeName from RoleInfo where RoleSid in (select * from f_splitstring('1,2,3',',')) for xml path('')),1,1,'')
-- 字符串转换为DataTable ,以方便查询用
create Function StrToTable(@str varchar(1000),@splitStr nvarchar(50))
Returns @tableName Table
(
str2table varchar(50)
)
As
Begin
if(LEN(@splitStr) =0)
begin
set @splitStr = ','
end
set @str = @str+@splitStr
Declare @insertStr varchar(50) --截取后的第一个字符串
Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(@splitStr,@str)-1)
set @newstr = stuff(@str,1,charindex(@splitStr,@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
set @insertStr = left(@newstr,charindex(@splitStr,@newstr)-1)
Insert @tableName Values(@insertStr)
set @newstr = stuff(@newstr,1,charindex(@splitStr,@newstr),'')
end
Return
End