存储过程返回字符串作为IN查询参数无效问题排查

存储过程及问题复现 #

我编写了如下存储过程：

ALTER PROCEDURE [dbo].[GetPhysicalChildNodesAsString]
    -- 存储过程参数
    @Book varchar(50),
    @Vertical varchar(50)
AS
BEGIN
    DECLARE @results nvarchar(max)
;WITH cte AS 
 (
  SELECT a.BookID, a.ParentBookID, a.BookName
  FROM DimBook a
  WHERE BookName = @Book
  UNION ALL
  SELECT a.BookID, a.ParentBookID, a.BookName
  FROM DimBook a JOIN cte c ON a.ParentBookID = c.BookID
  and HighPortfolioID = ( select PortfolioID from portfolioMaster where PortfolioName = @Vertical and PortfolioType='HighPortfolio')
  )
  select @results = coalesce(@results + ',', '')+'''' +  convert(varchar(max),BookName)+''''
  from cte Where Len(BookName) < 20

  select @results as Book

END

该存储过程返回的字符串格式如下：

'Physical','Anish Vohra','Sandeep Bajoria','Nirav Desai','Sushil Mohta','Sahil Pasad','G R Poddar','Direct','Sales Broker','Internal Transfer','Sprint','Murji Meghan','Intra Oils & Fats','GGN','GGN1','GGN2','Book1','Book2','Book 3','Book4','Book5','Comglobal','Sunvin','PVOC','Afro Asian'

无效的查询方式 #

执行以下查询时，仅返回表表头，没有数据：

DECLARE @BooksList varchar(max) 
DECLARE @t table(BookNames varchar(max) )
   INSERT @t(BookNames)
   EXEC @BooksList = GetPhysicalChildNodesAsString 'Physical','Enterprise'
   
   SELECT @BooksList = BookNames FROM @t

   select @BooksList as 'books'

   select * from DimBook where BookName IN(select @BooksList as 'books')

有效的查询方式 #

直接将上述字符串传入IN子句时，查询结果正常：

select * from DimBook where BookName IN('Physical','Anish Vohra','Sandeep Bajoria','Nirav Desai','Sushil Mohta','Sahil Pasad','G R Poddar','Direct','Sales Broker','Internal Transfer','Sprint','Murji Meghan','Intra Oils & Fats','GGN','GGN1','GGN2','Book1','Book2','Book 3','Book4','Book5','Comglobal','Sunvin','PVOC','Afro Asian')

原因分析 #

IN(select @BooksList as 'books')的执行逻辑是：把变量@BooksList的整个字符串当成单个值，去匹配DimBook表中的BookName字段。而表里不存在任何一条BookName等于这个完整长字符串的记录，所以自然返回空结果。

直接写字符串时，数据库会把逗号分隔的每个单引号包裹内容识别为独立的匹配值，所以能正确查询。

解决方法 #

方法1：修改存储过程，返回多行结果而非拼接字符串 #

这是最稳妥的方案，直接让存储过程返回符合条件的所有BookName，调用时直接用IN子句关联：

ALTER PROCEDURE [dbo].[GetPhysicalChildNodes]
    @Book varchar(50),
    @Vertical varchar(50)
AS
BEGIN
;WITH cte AS 
 (
  SELECT a.BookID, a.ParentBookID, a.BookName
  FROM DimBook a
  WHERE BookName = @Book
  UNION ALL
  SELECT a.BookID, a.ParentBookID, a.BookName
  FROM DimBook a JOIN cte c ON a.ParentBookID = c.BookID
  and HighPortfolioID = ( select PortfolioID from portfolioMaster where PortfolioName = @Vertical and PortfolioType='HighPortfolio')
  )
  SELECT BookName
  FROM cte 
  WHERE Len(BookName) < 20
END

调用示例：

-- 直接用EXEC作为IN子句的数据源
SELECT * 
FROM DimBook 
WHERE BookName IN(EXEC GetPhysicalChildNodes 'Physical','Enterprise')

-- 或者用临时表接收后查询
DECLARE @t table(BookNames varchar(max))
INSERT INTO @t
EXEC GetPhysicalChildNodes 'Physical','Enterprise'

SELECT * 
FROM DimBook 
WHERE BookName IN(SELECT BookNames FROM @t)

方法2：使用动态SQL执行查询 #

如果必须保留原存储过程的返回格式，可以用动态SQL把字符串拼接到查询语句中执行：

DECLARE @BooksList varchar(max) 
DECLARE @t table(BookNames varchar(max))
INSERT INTO @t(BookNames)
EXEC GetPhysicalChildNodesAsString 'Physical','Enterprise'

SELECT @BooksList = BookNames FROM @t

DECLARE @sql nvarchar(max)
SET @sql = N'SELECT * FROM DimBook WHERE BookName IN(' + @BooksList + N')'
EXEC sp_executesql @sql

⚠️ 注意：使用动态SQL要警惕SQL注入风险，如果@Book或@Vertical参数来自用户输入，必须确保参数经过安全校验。

方法3：用字符串拆分函数（SQL Server 2016+） #

如果你的SQL Server版本是2016及以上，可以用STRING_SPLIT函数拆分字符串后查询：

DECLARE @BooksList varchar(max) 
DECLARE @t table(BookNames varchar(max))
INSERT INTO @t(BookNames)
EXEC GetPhysicalChildNodesAsString 'Physical','Enterprise'

SELECT @BooksList = BookNames FROM @t
-- 清理字符串中的单引号，转换为适合拆分的格式
SET @BooksList = REPLACE(@BooksList, '''', '')

SELECT * 
FROM DimBook 
WHERE BookName IN(SELECT value FROM STRING_SPLIT(@BooksList, ','))

这种方法需要处理字符串格式，不如前两种可靠，仅适合临时场景。

内容的提问来源于stack exchange，提问作者venkat