Dynamically Build SQL Query based on table and column information stored in a Table

Question

There is a table with list of columns and table names.

I have to write a query to list form a SELECT Query.

example: This information i am extracting from a JSON. Now i have to build a SQL Statement.

ROW_ID  predicateName   EntityName
1       AGE            tbl_PR_AGE_VALIDATION_RULE
2       SALARY         tbl_PR_AGE_VALIDATION_RULE
3       GENDER         tbl_PR_AGE_VALIDATION_RULE
4       DATEOBIRTH     tbl_PR_AGE_VALIDATION_RULE
5       CITY           tbl_PR_AGE_VALIDATION_RULE
6       TEST1          tbl_PR_PAGE_VALIDATION_RULE
7       TEST2          tbl_PR_CITY_VALIDATION

AGE check in all list of table if it is a column of which pariticular table and create an ALIAS name for the same.

tbl_PR_AGE_VALIDATION_RULE A

SALARY check in all list of table if it is a column of which pariticular table and create an ALIAS B for the same.

tbl_PR_PAGE_VALIDATION_RULE A

FINAL QUERY SHOULD BE

SELECT A.AGE,B.SALARY FROM tbl_PR_AGE_VALIDATION_RULE A LEFT JOIN tbl_PR_PAGE_VALIDATION_RULE B
ON A.ENTITY_ID =B.ENTITY_ID 

ENTITY_ID is common all tables.

Below is what i tried.

DECLARE @TBL_PRD_ENT table (ROW_ID int IDENTITY(1, 1),
                            predicateName varchar(255),
                            EntityName varchar(255));
INSERT INTO @TBL_PRD_ENT
SELECT predicateNameEn,
       EntityName
FROM ##DTS_Parser_predicates P
     LEFT JOIN ##DTS_Parser_Datadefinition_Predicates DP ON P.predicateNameEn = DP.name
     INNER JOIN ##DTS_Parser_Datadefinition PD ON PD.ID = DP.ID;

SELECT *
FROM @TBL_PRD_ENT;
DECLARE @CT int,
        @ICT int,
        @I int = 1,
        @J int = 1;
SET @CT = (SELECT COUNT(1)FROM @TBL_PRD_ENT);
SET @ICT = (SELECT COUNT(DISTINCT EntityName)FROM @TBL_PRD_ENT);
SELECT @CT;
SELECT @ICT;
DECLARE @FINAL_TBL table (COL varchar(255),
                          TBL varchar(255));
WHILE @I <= @CT
BEGIN
    WHILE @J <= @ICT
    BEGIN
        INSERT INTO @FINAL_TBL
        SELECT TABLE_NAME + '.' + COLUMN_NAME,
               TABLE_NAME + '  ' + TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = (SELECT predicateName FROM @TBL_PRD_ENT WHERE ROW_ID = @I)
          AND TABLE_NAME = (SELECT EntityName FROM @TBL_PRD_ENT WHERE ROW_ID = @I);
        SET @J = @J + 1;
    END;
    SET @I = @I + 1;
    SET @J = 1;
END;
DECLARE @SQL_COL nvarchar(MAX);
DECLARE @SQL_TBL nvarchar(MAX);

SELECT @SQL_COL = N'SELECT ' + STRING_AGG(COL, ',') + ' FROM '
FROM @FINAL_TBL;
WITH
CTE AS
    (SELECT DISTINCT
            TBL
     FROM @FINAL_TBL)
SELECT @SQL_TBL = STRING_AGG(TBL, ' LEFT JOIN ')
FROM CTE;




SELECT @SQL_COL,
       @SQL_TBL;

output

SELECT TBL_PR_AGE_VALIDATION_RULE.AGE,TBL_PR_AGE_VALIDATION_RULE.SALARY,TBL_PR_AGE_VALIDATION_RULE.GENDER,TBL_PR_AGE_VALIDATION_RULE.DATEOBIRTH,TBL_PR_AGE_VALIDATION_RULE.CITY,tbl_PR_PAGE_VALIDATION_RULE.TEST1,tbl_PR_CITY_VALIDATION.TEST2 FROM 

TBL_PR_AGE_VALIDATION_RULE  TBL_PR_AGE_VALIDATION_RULE
tbl_PR_PAGE_VALIDATION_RULE tbl_PR_PAGE_VALIDATION_RULE
tbl_PR_CITY_VALIDATION      tbl_PR_CITY_VALIDATION

expected output:

SELECT TBL_PR_AGE_VALIDATION_RULE.AGE,
       TBL_PR_AGE_VALIDATION_RULE.SALARY,
       TBL_PR_AGE_VALIDATION_RULE.GENDER,
       TBL_PR_AGE_VALIDATION_RULE.DATEOBIRTH,
       TBL_PR_AGE_VALIDATION_RULE.CITY,
       tbl_PR_PAGE_VALIDATION_RULE.TEST1,
       tbl_PR_CITY_VALIDATION.TEST2
FROM TBL_PR_AGE_VALIDATION_RULE TBL_PR_AGE_VALIDATION_RULE
     LEFT JOIN tbl_PR_PAGE_VALIDATION_RULE tbl_PR_PAGE_VALIDATION_RULE ON TBL_PR_AGE_VALIDATION_RULE.ENTITY_ID = tbl_PR_PAGE_VALIDATION_RULE.ENTITY_ID
     LEFT JOIN tbl_PR_CITY_VALIDATION tbl_PR_CITY_VALIDATION ON tbl_PR_PAGE_VALIDATION_RULE.ENTITY_ID = tbl_PR_CITY_VALIDATION.ENTITY_ID;

I am not able to inject LEFT JOIN ON ALIAS.ENTITY_ID=ALIAS.ENTITY_ID in my output. Kindly Help.

Answer 1

The trick of selecting into a variable to build a string still works fine for this.

In the SQL snippet below the aliasnames are calculated first into a table variable. To reuse them for the tables and the fields.

-- Sample data
declare @TBL_PRD_ENT table (ROW_ID int identity(1,1) primary key, 
predicateName varchar(16) not null, 
EntityName varchar(80) not null);
insert into @TBL_PRD_ENT (predicateName, EntityName) values
('AGE', 'tbl_PR_AGE_VALIDATION_RULE')
, ('SALARY', 'tbl_PR_AGE_VALIDATION_RULE')
, ('GENDER', 'tbl_PR_AGE_VALIDATION_RULE')
, ('DATEOBIRTH', 'tbl_PR_AGE_VALIDATION_RULE')
, ('CITY', 'tbl_PR_AGE_VALIDATION_RULE')
, ('TEST1', 'tbl_PR_PAGE_VALIDATION_RULE')
, ('TEST2', 'tbl_PR_CITY_VALIDATION')
;

-- Using a table variable to calculate the alias names
-- So that the calculated aliases can also be used for the fields.
declare @TableAliases table (EntityName varchar(80), Alias char(1) not null);
insert into @TableAliases (EntityName, Alias)
select EntityName, char(64+row_number() over (order by min(ROW_ID))) as JoinAlias
from @TBL_PRD_ENT
group by EntityName
order by min(ROW_ID);

declare @Cols varchar(max);
select @Cols = concat(@Cols +','+char(10)+REPLICATE(' ',7), a.Alias+'.'+QUOTENAME(e.predicateName))
from @TBL_PRD_ENT e
join @TableAliases a ON a.EntityName = e.EntityName
order by e.ROW_ID;

declare @Joins varchar(max);
select @Joins = concat(@Joins + char(10) + 'LEFT JOIN ', QUOTENAME(EntityName), ' AS '+Alias, ' ON '+Alias+'.ENTITY_ID = '+LAG(Alias) OVER (ORDER BY Alias)+'.ENTITY_ID')
from @TableAliases
order by Alias;

-- Putting it all together
declare @DynSql nvarchar(max);
SET @DynSql = 'SELECT ' + @Cols + char(10) + 'FROM ' + @Joins;

select @DynSql as DynSql;

Returns:

SELECT A.[AGE],
       A.[SALARY],
       A.[GENDER],
       A.[DATEOBIRTH],
       A.[CITY],
       B.[TEST1],
       C.[TEST2]
FROM [tbl_PR_AGE_VALIDATION_RULE] AS A
LEFT JOIN [tbl_PR_PAGE_VALIDATION_RULE] AS B ON B.ENTITY_ID = A.ENTITY_ID
LEFT JOIN [tbl_PR_CITY_VALIDATION] AS C ON C.ENTITY_ID = B.ENTITY_ID

A test on rextester here

Answer 2

try this,

DECLARE @TEMP_TABLE TABLE
(
    table_ID INT,
    predicateName VARCHAR(250),
    EntityName VARCHAR(250)
)

INSERT INTO @TEMP_TABLE
(
    table_ID,predicateName,EntityName
)
SELECT 1,'AGE','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'SALARY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'GENDER','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'DATEOBIRTH','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'CITY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 2,'TEST1','tbl_PR_PAGE_VALIDATION_RULE' UNION ALL
SELECT 3,'TEST2','tbl_PR_CITY_VALIDATION'

DECLARE @VC_COLUMN_LIST VARCHAR(2000)='',@VC_TABLE_LIST VARCHAR(2000)=''

SELECT  @VC_COLUMN_LIST+=EntityName+'.'+predicateName+','+CHAR(10)
FROM @TEMP_TABLE

SELECT @VC_COLUMN_LIST=LEFT(@VC_COLUMN_LIST,LEN(@VC_COLUMN_LIST)-2)

;WITH CTE_TEMP AS
(
        SELECT DISTINCT table_ID, EntityName
        FROM @TEMP_TABLE
)
SELECT @VC_TABLE_LIST+=IIF(B.table_ID IS NULL,'','LEFT JOIN ')+A.EntityName+IIF(B.table_ID IS NULL,'',' ON '+A.EntityName+'.ENTITY_ID = '+B.EntityName+'.ENTITY_ID')+CHAR(10) 
FROM CTE_TEMP A
LEFT JOIN CTE_TEMP B ON A.table_ID=B.table_ID+1 

PRINT
(
    'SELECT '+@VC_COLUMN_LIST+CHAR(10)+
    'FROM '+@VC_TABLE_LIST
)

Answer 3

Using SQL Server 2017 means you can use the (finally!) built in string_agg function to help with at least a part of the job creating the dynamic SQL for you.
As a rule, using loops in SQL is the wrong thing to do, since SQL works best with a set based approach rather than a procedural approach (A.K.A. RBAR).
Having said that, here's what I would do in your situation:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    ROW_ID  int,
    predicateName sysname,
    EntityName sysname
);

INSERT INTO @T (ROW_ID, predicateName, EntityName) VALUES
(1, 'AGE', 'tbl_PR_AGE_VALIDATION_RULE'),
(2, 'SALARY', 'tbl_PR_AGE_VALIDATION_RULE'),
(3, 'GENDER', 'tbl_PR_AGE_VALIDATION_RULE'),
(4, 'DATEOBIRTH', 'tbl_PR_AGE_VALIDATION_RULE'),
(5, 'CITY', 'tbl_PR_AGE_VALIDATION_RULE'),
(6, 'TEST1', 'tbl_PR_PAGE_VALIDATION_RULE'),
(7, 'TEST2', 'tbl_PR_CITY_VALIDATION');

Then, the main table name parameter:

DECLARE @TableName sysname = 'tbl_PR_AGE_VALIDATION_RULE';

Now, let's declare some variables - One named @Sql to hold the dynamic sql we're building, and a few more to help us format it into a readable SQL Statement. This is very important since the first thing you want to do with dynamic SQL is to print it and see what you get, before actually executing it:

DECLARE @Sql nvarchar(max), 
    @Tab nchar = NCHAR(9),
    @LineBreak nchar(2) = NCHAR(13) + NCHAR(10),
    @ColumnSeperaor NCHAR(4);

SET @ColumnSeperaor = ',' + @LineBreak + @Tab;

Now the real fun begins - first, get the column names from your table, with their table references:

SELECT @Sql = 'SELECT '+ STRING_AGG(QUOTENAME(EntityName) + '.'+ QUOTENAME(predicateName), @ColumnSeperaor) + @LineBreak
FROM @T

At this point, your @Sql variable holds a nicely formatted select statement, only without the from and join clauses - so let's add the from clause as well:

SELECT @Sql = @Sql + 'FROM '+ QUOTENAME(@TableName) + @LineBreak

Now we add the left join clauses to your query. Please note that here we can't use the string_agg function because it doesn't support concatenated strings as the separator argument, so we go to the older alternative - for xml path:

SET @Sql = @Sql + 
(
    SELECT DISTINCT 'LEFT JOIN ' + QUOTENAME(EntityName) + @LineBreak + @Tab + 'ON '+ QUOTENAME(@TableName) + '.ENTITY_ID = '+ QUOTENAME(EntityName) +'.ENTITY_ID ' + @LineBreak 
    FROM @T
    WHERE EntityName <> @TableName
    FOR XML PATH(''),TYPE 
).value('.[1]','varchar(max)')

Finally, let's print the entire sql we've just created so that we can see if everything is alright:

PRINT @Sql

Which results with a nicely formatted readable SQL:

SELECT [tbl_PR_AGE_VALIDATION_RULE].[AGE],
    [tbl_PR_AGE_VALIDATION_RULE].[SALARY],
    [tbl_PR_AGE_VALIDATION_RULE].[GENDER],
    [tbl_PR_AGE_VALIDATION_RULE].[DATEOBIRTH],
    [tbl_PR_AGE_VALIDATION_RULE].[CITY],
    [tbl_PR_PAGE_VALIDATION_RULE].[TEST1],
    [tbl_PR_CITY_VALIDATION].[TEST2]
FROM [tbl_PR_AGE_VALIDATION_RULE]
LEFT JOIN [tbl_PR_CITY_VALIDATION]
    ON [tbl_PR_AGE_VALIDATION_RULE].ENTITY_ID = [tbl_PR_CITY_VALIDATION].ENTITY_ID 
LEFT JOIN [tbl_PR_PAGE_VALIDATION_RULE]
    ON [tbl_PR_AGE_VALIDATION_RULE].ENTITY_ID = [tbl_PR_PAGE_VALIDATION_RULE].ENTITY_ID 

Now that we can verify that the dynamic SQL statement looks good, you can try running it and see if it actually does what you expect. Once it does, all you have to do is replace the print with exec:

EXEC(@Sql);

Please note, however, that even with the usage of quotename everywhere, if you're getting that list of columns and tables from an outside source, this code along might be vulnerable to SQL injection attacks - which means you must first verify that all the inputs are valid by white-listing against system tables or views - such as infromation_schema.Columns.

For more information, check out The do’s and don’ts of dynamic SQL for SQL Server over on my blog.

You can see a live demo of the entire script, including prints of each part of the way over on db<>fiddle