Select only those columns from a table header which are present in data dictionary ColumnName-CodePudding

Prerequisite : all the tables are dynamic so i cant use the column names I have two tables

Candidate table : Table which has all columns and data required to be selected

DataDictionary : Table where i have only those columns which are to be selected for querying Now what i want to do is select only that data and columns from the candidate table which are present in datadictionary and skip those that data and column which are not present in datadictionary

what i have tried is

SELECT ColumnName
INTO #Candidate
FROM DataDictionaryDetail WHERE DataDictionaryId =1

select *
from candidate
where NOT EXISTS (select *from #Candidate)

but this brings only columns but not data I need a proper way to select data also and columns

CodePudding user response：

You need dynamic SQL for this

DECLARE @sql nvarchar(max) = N'
SELECT
  '
  (
    SELECT STRING_AGG(QUOTENAME(ColumnName), ',')
    FROM DataDictionaryDetail
    WHERE DataDictionaryId = 1
)   N'
from candidate;
';

EXEC sp_executesql @sql;