0
162
ViewsTSQL to generate the dynamic SQL statement before execution
If I have a list of column names a,b,c is it possible to generate a SQL statement as string (to be executed inside EXEC as a dynamic SQL) based on the number of items in that column names.
So, if I have a table called @src
DECLARE @src AS TABLE
(
list VARCHAR(max)
)
INSERT INTO @src
SELECT 'a'
UNION
SELECT 'b'
UNION
SELECT 'c'
SELECT *
FROM @src
how can I generate a string dynamically for each element in @src to give a string a,b,c
which will be further utilized inside a EXEC statement like EXEC('select'+a,b,c(coming from dynamic string)+'from mainTbl
To better demonstrate this with a javascript string generator
var src = ['a','b','c'];
var target =['e','f','g'];
var beginning ='select';
var end = 'from mainTbl';
var newArray=[];
src.forEach((x,i,r)=>{newArray.push(x+' '+'as '+target[i]);});
var dynamicStr = beginning+' '+newArray.reduce(function(prev,current){return prev+', '+current;})+' '+end; //to be utilized inside SQL EXEC
This is in javascript but is it possible for TSQL to generate this string for each element of @src so that whenever I edit @src the final result dynamically changes.
1 answers
Answer question0
If I understand correctly it looks like you just need to aggregate your "columns".
In SQLServer 2017+ you can use string_agg()
SELECT Concat('select ', String_Agg(quotename(list),', '), ' from t')
FROM @src
Prior to 2017 you can use the clunkier for xml path syntax
select Concat('select ',
Stuff((
select ', ' + QuoteName(list)
from @src
for xml path(''), type).value('text()[1]','nvarchar(max)'), 1, len(', '), ''),
' from t'
) as statement