SQL Server类数组实现动态查询及变量赋值错误解决求助

错误原因 #

Msg 141, Level 15, State 1, Line 15
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

这个错误是因为你试图通过SELECT @delta = ... FROM ...同时完成变量赋值和多行数据检索，SQL Server不允许这种操作。要生成拼接后的加权计算表达式，需要用字符串聚合的方式把多行权重规则合并成一个完整的字符串。

修正后的单类别查询示例 #

直接针对指定Category生成动态SQL，无需临时表：

/* Dummy Table Creation */
DECLARE @DummyWeightageTable TABLE (Category varchar(50), Fieldname varchar(50), Weightage real)
INSERT INTO @DummyWeightageTable 
VALUES ('Sniper', 'Eyesight', 40), 
       ('Sniper', 'BMI', 10), 
       ('Sniper', 'Height', 10), 
       ('Sniper', 'Skill', 20), 
       ('Fighter', 'Eyesight', 10), 
       ('Fighter', 'BMI', 30), 
       ('Fighter', 'Height', 20)

/* 生成单个Category的查询 */
DECLARE @TargetCategory varchar(50) = 'Sniper'
DECLARE @sql VARCHAR(MAX)

-- 核心：用STUFF + FOR XML PATH拼接加权表达式
SELECT @sql = 'SELECT Emp_Id, (' + STUFF(
    (SELECT ' + ' + CAST(Weightage AS VARCHAR(10)) + ' * ISNULL(' + Fieldname + ', 0)'
     FROM @DummyWeightageTable 
     WHERE Category = @TargetCategory
     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
    1, 3, '') + ') AS WeightedScore FROM MyTable1 WHERE Category = ''' + @TargetCategory + ''''

-- 执行动态SQL
EXEC sp_executesql @sql

批量处理所有Category的版本 #

如果有上百个Category，用游标自动遍历生成所有查询：

/* Dummy Table Creation */
DECLARE @DummyWeightageTable TABLE (Category varchar(50), Fieldname varchar(50), Weightage real)
INSERT INTO @DummyWeightageTable 
VALUES ('Sniper', 'Eyesight', 40), 
       ('Sniper', 'BMI', 10), 
       ('Sniper', 'Height', 10), 
       ('Sniper', 'Skill', 20), 
       ('Fighter', 'Eyesight', 10), 
       ('Fighter', 'BMI', 30), 
       ('Fighter', 'Height', 20)

/* 批量生成所有类别的查询 */
DECLARE @CurrentCategory varchar(50)
DECLARE CategoryCursor CURSOR FOR
SELECT DISTINCT Category FROM @DummyWeightageTable

OPEN CategoryCursor
FETCH NEXT FROM CategoryCursor INTO @CurrentCategory

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql VARCHAR(MAX)
    SELECT @sql = 'SELECT Emp_Id, (' + STUFF(
        (SELECT ' + ' + CAST(Weightage AS VARCHAR(10)) + ' * ISNULL(' + Fieldname + ', 0)'
         FROM @DummyWeightageTable 
         WHERE Category = @CurrentCategory
         FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
        1, 3, '') + ') AS WeightedScore FROM MyTable1 WHERE Category = ''' + @CurrentCategory + ''''
    
    -- 可选：先打印SQL确认语法正确性
    -- PRINT @sql
    EXEC sp_executesql @sql

    FETCH NEXT FROM CategoryCursor INTO @CurrentCategory
END

CLOSE CategoryCursor
DEALLOCATE CategoryCursor

关键说明 #

• FOR XML PATH(''), TYPE：将多行结果拼接成XML字符串，TYPE.value('.', 'VARCHAR(MAX)')避免特殊字符被转义（比如&变成&）
• STUFF(..., 1, 3, '')：移除拼接后字符串开头多余的+
• 给加权计算结果命名为WeightedScore，方便后续使用
• 游标遍历自动处理所有Category，无需手动编写每个类别的查询逻辑

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