在where子句中使用' in‘返回子查询返回多个值

Question

当我使用params正常运行时，查询运行正常。但是，一旦我将其动态化，它就会导致错误。

我对SQL server还是个新手。当我打印并运行查询时，它工作得很好。但当动态运行时，它会导致‘子查询返回多于1个值’错误。

declare @query nvarchar(max) =  'Select * 
                    into #tempFilteredData              
                    from 
                    (
                    SELECT td.UserId AS [EmployeeID],w.NAME AS [EmployeeName],at.ActivityCode AS [ActivityType],td.TargetAssigned,td.Mkt_TerritoryId,
                    wd.WeekName + ''('' + CONCAT((RTRIM(CONVERT(CHAR(3),DATENAME(day, wd.WeekStartDate),0))), ''-'', CONVERT(CHAR(3),DATENAME(month, wd.WeekStartDate),0),''To'',
                    (RTRIM(CONVERT(CHAR(3),DATENAME(day, wd.WeekEndDate),0))), ''-'', CONVERT(CHAR(3),DATENAME(month, wd.WeekEndDate),0)) + '')'' AS weekName
                    FROM tblTargetDetails td
                    INNER JOIN FMC_CMaster..Worker w ON td.UserId = w.[Personnel Number]
                    INNER JOIN FMC_CMaster..[vwAuthenticateUser] vw ON vw.UserId = td.UserId and vw.Blocked=''No'' and vw.Closed=''No'' AND vw.AssetCode = ''MPH''
                    INNER JOIN tblActivityType at ON at.ActivityTypeId = td.ActivityTypeId
                    INNER JOIN WeekDetails wd ON wd.WeekId = td.WeekId
                    LEFT JOIN FMC_CMaster..Marketing_Hierarchy_AMM_Level mrktHAL ON w.[Personnel Number] = mrktHAL.TMH_Code 
                    WHERE td.CreatedBy =  ''' + @LoggedInUserId + '''  
                    AND mrktHAL.TMH_Code = ''' + @TMHCode + '''  

                    ) as p 
                    pivot(
                    MAX([TargetAssigned]) for [WeekName] in ('+RTRIM(LTRIM(@columnName))+')
                    ) as pvt

                    select * from #tempFilteredData where Mkt_TerritoryId IN ( '+(SELECT [MktTerritoryName] FROM @MktTerritoryNames)+' )'
        PRINT (@query)
        exec (@query)

我想在我的临时表#tempFilteredData中搜索@MktTerritoryNames返回的所有结果

Answer 1

第一个查询表达式中没有子查询。

这就导致了第二个问题。这在表达式中是做什么的？

select * from #tempFilteredData where Mkt_TerritoryId IN ( '+(SELECT [MktTerritoryName] FROM @MktTerritoryNames)+' )'

我甚至不明白这是怎么回事。也许您想在创建临时表之后运行它。而且，它不需要是动态的，只需要：

select *
from #tempFilteredData
where Mkt_TerritoryId IN (SELECT [MktTerritoryName] FROM @MktTerritoryNames);