如何改写含UDF的INSERT INTO SELECT子查询使其返回单个值

问题场景 #

需要通过INSERT INTO SELECT语句创建新表并迁移旧表数据，核心需求是从VARCHAR类型的duration字段（示例值：'Concentration, up to 1 hour'）中提取数字，按天、小时、分钟换算为统一数值后插入新表。已使用自定义函数dbo.getNumericValue提取数字，该函数单独执行SELECT查询时正常，但作为子查询表达式使用时，因返回多行结果报错；尝试用EXEC替代SELECT也出现语法错误，需要改写子查询使其返回单个值，或更优实现方案。

原错误代码片段：

CASE
    WHEN s.duration IN ('Instantaneous')
        THEN '0'
  
    WHEN s.duration LIKE '%round%'
        THEN '1'
  
    WHEN s.duration LIKE '%minute%'
        THEN (SELECT dbo.getNumericValue(duration) * 10 
              FROM Spell.Spells 
              WHERE duration LIKE '%minute%')                                                                                                                                  
    
    WHEN s.duration LIKE '%hour%'
        THEN  (SELECT dbo.getNumericValue(duration) * 600 
               FROM Spell.Spells  
               WHERE duration LIKE '%hour%')

    WHEN s.duration LIKE '%day%'
        THEN (SELECT dbo.getNumericValue(duration) * 14400 
              FROM Spell.Spells 
              WHERE duration LIKE '%day%')

    ELSE s.duration
END AS duration,
ritual,
verbal,
somatic,
material,
material_component,
material_cost,
material_consumed,
description,
source
FROM 
    Spell.Spells AS s

错误原因 #

原代码中每个WHEN分支的子查询都是查询整个Spell.Spells表中符合条件的所有行，返回多行结果，但CASE表达式的每个分支要求返回单个值，因此触发“子查询返回多行”的错误。

解决方案 #

直接针对当前行的s.duration字段调用dbo.getNumericValue函数，无需嵌套子查询查询整张表。因为CASE语句是逐行处理Spell.Spells AS s中的每条数据，直接引用当前行的字段即可：

修改后的代码：

CASE
    WHEN s.duration = 'Instantaneous'
        THEN '0'

    WHEN s.duration LIKE '%round%'
        THEN '1'

    WHEN s.duration LIKE '%minute%'
        THEN CAST(dbo.getNumericValue(s.duration) * 10 AS VARCHAR(20))
    
    WHEN s.duration LIKE '%hour%'
        THEN CAST(dbo.getNumericValue(s.duration) * 600 AS VARCHAR(20))

    WHEN s.duration LIKE '%day%'
        THEN CAST(dbo.getNumericValue(s.duration) * 14400 AS VARCHAR(20))

    ELSE s.duration
END AS duration,
ritual,
verbal,
somatic,
material,
material_component,
material_cost,
material_consumed,
description,
source
FROM 
    Spell.Spells AS s

关键改动说明 #

1. 移除多余子查询：将每个时间单位分支中的子查询替换为直接调用dbo.getNumericValue(s.duration)，确保只处理当前行的duration值，返回单个结果。
2. 统一数据类型：使用CAST(...) AS VARCHAR(20)将计算后的数值转换为字符串类型，与其他分支返回的'0'、'1'以及ELSE分支的duration字段类型保持一致，避免类型不匹配问题。
3. 简化条件判断：将IN ('Instantaneous')改为= 'Instantaneous'，逻辑更简洁。

额外优化建议 #

如果duration字段中可能存在多个数字（比如'2 hours and 30 minutes'），需要确认dbo.getNumericValue函数是否能正确提取目标数字；如果函数只能提取第一个数字，可能需要调整函数逻辑或使用SQL Server的PATINDEX结合SUBSTRING来精准提取所需数值。

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