Dynamic SQL created with array of values

Question

I have a list of column names, for example ('Name', 'Code', 'Title') that can be array but not necessarily, it can be anything. I just have a list of column names.

Then I have to create a dynamic SQL select that has to be executed later. That dynamic SQL select is generating XML, so it uses for xml path, here is a simplified version:

@sql = select @column_name, some_fixed_column
       from t_table
       where some_Condition
       for xml path;

this has to be dynamic query plus it creates XML, but I want to make it somehow go through all column names from the list, so the general outcome would be something like:

select Name, some_fixed_column from tb_table where some_Condition for xml path;
select Code, some_fixed_column from tb_table where some_Condition for xml path;
select Title, some_fixed_column from tb_table where some_Condition for xml path;

I can't even use + or Union because it doesn't work with for xml path queries (I tried). The query is quite complicated and it already works on multiple XML levels. Is this even possible?

You can create a loop through all the columns you want to be dynamic and create/run each statement in the loop as you loop through. You can create dynamic SQL but you have to put single quotes around it. Then for the variable you have to use +. Short something like this SQL = 'Select ' + @column_name + ' rest of your dynamic code ' Then execute the sql variable — Brad
– Brad, Commented Aug 30, 2024 at 12:43
You can use UNPIVOT to generate multiple rows, either by the UNPIVOT operator or by a CROSS APPLY VALUES construct — siggemannen
– siggemannen, Commented Aug 30, 2024 at 12:51
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Aug 30, 2024 at 13:44

user25043454 · Accepted Answer · 2024-08-30 12:59:26Z

2

In SQL Server 2017 or newer, one way would be to use STRING_AGG() against the set of column names.

SQL Server doesn't have arrays. One way to simulate an array is simply a comma-separated list, and break them apart with STRING_SPLIT(), like so:

DECLARE @sql nvarchar(max), 
  
        @ColumnNames nvarchar(max) 
        = N'Name,Code,Title',
  
        @QueryTemplate nvarchar(max) 
        = N'SELECT %col%, fixed_column FROM whatever FOR XML PATH;';

SELECT @sql = STRING_AGG(REPLACE(@QueryTemplate, '%col%', value), 
       char(13) + char(10))     
  FROM STRING_SPLIT(@ColumnNames, ',');

PRINT @sql;
-- execute it using EXEC sys.sp_executesql @sql;

Result:

SELECT Name, fixed_column FROM whatever FOR XML PATH;
SELECT Code, fixed_column FROM whatever FOR XML PATH;
SELECT Title, fixed_column FROM whatever FOR XML PATH;

Example db<>fiddle

Or if you have them in a set, it's even simpler:

DECLARE @Columns table(name sysname); -- or a TVP

INSERT @Columns(name) VALUES(N'Name'),(N'Code'),(N'Title');

DECLARE @sql nvarchar(max), 
  
        @QueryTemplate nvarchar(max) 
        = N'SELECT %col%, fixed_column FROM whatever FOR XML PATH;';

SELECT @sql = STRING_AGG(REPLACE(@QueryTemplate, '%col%', name), 
       char(13) + char(10))     
  FROM @Columns;

PRINT @sql;
-- execute it using EXEC sys.sp_executesql @sql;

Example db<>fiddle

answered Aug 30, 2024 at 12:59

user25043454

Sign up to request clarification or add additional context in comments.

1 Comment

Biljana M. Over a year ago

I had a good feeling about this solution but sadly my version of SQL Management server doesn't have STRIG_AGG and STRING_SPLIT

Collectives™ on Stack Overflow

Dynamic SQL created with array of values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related