Dynamic query creation with Array like implementation

Question

I need to create multiple queries with various weightages and properties.

The simplified version of couple of queries is this

SELECT 
    Emp_Id,  
    (30 * ISNULL(BMI,0) + 
    (20 * ISNULL(Height, 0) + 
    (10 * ISNULL(Eyesight, 0)) 
FROM
    MyTable1 
WHERE
    Category = 'Fighter'

SELECT 
    Emp_Id,  
    (10 * ISNULL(BMI,0) + 
    (10 * ISNULL(Height,0) +
    (20 * ISNULL(Skill,0) + 
    (40 * ISNULL(Eyesight,0)) 
FROM
    MyTable1 
WHERE
    Category = 'Sniper'

There are 100s of queries with different weightages and properties. So I wanted to create a table with weightages and properties, then create dynamic query which would be executed since it will be much easier to maintain.

This is my code so far:

/* 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)

/* Actual Functionality */

DECLARE @sql VARCHAR(MAX)
DECLARE @delta VARCHAR(MAX)

DECLARE @TempTableVariable TABLE (Fieldname varchar(50), Weightage real)

INSERT INTO @TempTableVariable 
    SELECT Fieldname, Weightage 
    FROM @DummyWeightageTable 
    WHERE Category = 'Sniper'

SET @sql = 'SELECT Emp_Id,'

/*Do below step for all rows*/
SELECT @delta = '(', Weightage, ' * ISNULL(', Fieldname, ',0) +' 
FROM @TempTableVariable

SET @sql = @sql + @delta + '0) from MyDataTable1'

EXEC sp_executesql @sql;

TRUNCATE @TempTableVariable

INSERT INTO @TempTableVariable 
    SELECT Fieldname, Weightage 
    FROM @DummyWeightageTable 
    WHERE Category = 'Fighter'

SET @sql = 'SELECT Emp_Id,'

/*Do below step for all rows*/
SELECT @delta = '(', Weightage, ' * ISNULL(', Fieldname, ',0) +' 
FROM @TempTableVariable

SET @sql = @sql + @delta + '0) from MyDataTable1'

EXEC sp_executesql @sql;

However SQL Server doesn't allow arrays. So I am getting an error when I try to populate variable @delta

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.

I feel there must be some workaround for this but I couldn't find it.

Answer 1

There is so much wrong with your dynamic SQL that I won't list it. And I won't comment on your design, it doesn't sound optimal, but as say the problem is a more complex than described its hard to comment.

Anyway, with what you have provided you can do the following:

• You can loop through all categories to avoid having to manually code them
• At a row level you need to CONCAT the values to create a dynamic string
• At a multi-row level you need to STRING_AGG the rows to build a single row

/* 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);

/* Actual Functionality */

DECLARE @Category TABLE (
    Category varchar(50)
    , Done bit NOT NULL DEFAULT(0)
);

-- Capture all categories so we can loop through them
INSERT INTO @Category (Category)
SELECT Category
FROM @DummyWeightageTable
GROUP BY Category;
  
-- Dynamic SQL must use nvarchar
DECLARE @sql NVARCHAR(MAX), @delta NVARCHAR(MAX), @CurrentCategory varchar(50);

-- Loop through all categories
WHILE EXISTS (SELECT 1 FROM @Category WHERE Done = 0) BEGIN
    SELECT TOP 1 @CurrentCategory = Category FROM @Category WHERE Done = 0;
  
    -- Display the category at least for debugging
    SET @sql = 'SELECT Emp_Id, ' + @CurrentCategory + ', ';

    -- First concat the values required from each row
    -- Then string_agg then into a single string
    SELECT @delta = STRING_AGG(CONCAT('(', Weightage, ' * ISNULL(', Fieldname, ',0))'), ' + ')
    FROM @DummyWeightageTable
    WHERE Category = @CurrentCategory;

    SET @sql = @sql + @delta + ' from MyDataTable1;';

    -- This is how you debug dynamic SQL
    PRINT @sql;
    --EXEC sp_executesql @sql;

    UPDATE @Category SET Done = 1 WHERE Category = @CurrentCategory;
END;

Returns:

SELECT Emp_Id, (10 * ISNULL(Eyesight,0)) + (30 * ISNULL(BMI,0)) + (20 * ISNULL(Height,0)) from MyDataTable1;
SELECT Emp_Id, (40 * ISNULL(Eyesight,0)) + (10 * ISNULL(BMI,0)) + (10 * ISNULL(Height,0)) + (20 * ISNULL(Skill,0)) from MyDataTable1;

DBFiddle