TSQL to generate the dynamic SQL statement before execution-CodePudding

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.

CodePudding user response：

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