使用 id 时转换失败 "where ID in (...)" ， nvarchar 到 int

Question

>我在MSSQL 2008中有一个查询，如下所示：

IF OBJECT_Id('tempdb..#AccessibleFacilities') IS NOT NULL DROP TABLE #AccessibleFacilities
SELECT u.Userid
     , AccesibleFacilityIds = dbo.GetCommaDelimitedString(upf.Facility_Id)  
INTO #AccessibleFacilities   
FROM Users u
INNER join UserProfileFacilities upf on upf.UserProfile_Id = up.Id  
WHERE LOWER(u.Userid) = LOWER(@userId)
GROUP BY u.Userid

此查询返回类似 ",1,2,3,4,5,6," 的可访问设施 ID。请注意，我无法修改 GetCommaDelimitString 函数。

我实际需要做的是使用这些设施 ID 来达到如下所示的证据：

INSERT INTO #AccessibleProvs
    SELECT Userid = @userId
         , AccessibleProvIds = dbo.GetCommaDelimitedString(distinct p.Id) 
    FROM Provs p 
         inner join ProvFacs pf on p.Id = pf.Provider_Id
    WHERE pf.Facility_Id in 
          (select a.AccesibleFacilityIds from #AccessibleFacilities a) 

但是，它给了我一个错误，例如：

转换

nvarchar 值"，1,2,3,4,5,6"时转换失败 数据类型为 int。

我尝试删除开头和结尾的逗号符号，如下所示来修复它，但它没有帮助：

...
where pf.Facility_Id in (
      select SUBSTRING(a.AccesibleFacilityIds,2,LEN(a.AccesibleFacilityIds)-2) 
      from #AccessibleFacilities a
     )

任何建议将不胜感激。谢谢。

Answer 1

与其将Facility_Id转换为逗号分隔的字符串，为什么不将其保留为临时表中的可用列呢？

if object_Id('tempdb..#AccessibleFacilities') is not null drop table #AccessibleFacilities;
select 
    u.UserId
  , upf.Facility_Id
into #AccessibleFacilities
from Users u
  inner join UserProfileFacilities upf 
    on upf.UserProfile_Id = up.Id  

然后像使用 in() 或 exists() 一样使用它：

insert into #AccessibleProvs
select 
    UserId = @userId
  , AccessibleProvIds = dbo.GetCommaDelimitedString(distinct p.Id) 
from Provs p 
  inner join ProvFacs pf 
    on p.Id = pf.Provider_Id
where exists (
   select 1
   from #AccessibleFacilities a
   where a.Facility_Id = pf.Facility_Id
     --and a.UserId = @UserId -- Do you need to check Facility_Id by User?
   ) 

如果一开始就有 @UserId 的值，则可以将临时表的使用限制为仅所需的用户。希望此代码不适用于某种游标或其他循环。