Dynamically looping multiple tables to inner join clause

Question

I am in a situation where I have to pull around 30 columns from 20 different tables. All the 20 tables can be joined with 2 key columns. I want to replace the static join statements with dynamic join statements.

I am able to pull all the 20 table names and store it in a table variable. I am not able to append these table variable values( for table names) with the dynamic query.

    DECLARE @Table TABLE
    (
    TableName VARCHAR(50),Id int identity(1,1)
    )
    INSERT INTO @Table
    Select tablename From states 
    DECLARE @max int
    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableName VARCHAR(50)
    DECLARE @id int = 1 
    Declare @Param1 varchar(20) ='p1', @param2 varchar(20) ='p2'
    DECLARE @qry NVARCHAR(MAX)
    Declare @strcolumns nvarchar(max) = 'select c1, c2, c3, ...c30 from primarytable inner Join'  
---------------  
select @max = MAX(Id) from @Table
WHILE (@id <= @max)
BEGIN
SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =      @TableName +''
SET @qry = @strColumns + @SQL
PRINT(@qry)
SET @id = @id +1
END

below is the result of the query

select c1, c2, c3, ...c30 from primarytable inner Join tbl1
select c1, c2, c3, ...c30 from primarytable inner Join tbl2
select c1, c2, c3, ...c30 from primarytable inner Join tbl3
select c1, c2, c3, ...c30 from primarytable inner Join tbl4
select c1, c2, c3, ...c30 from primarytable inner Join tbl5
.... 
select c1, c2, c3, ...c30 from primarytable inner Join tbl20

I want the result set to be like

 select c1, c2, c3, ...c30 from
 primarytable P inner Join tbl1 on P.@p1 = tbl1.@p1  and p.@p2 = tbll.@p2
Inner join @tbl2 on p.@p1 = tbl2.@p1 and p.@p2 = tb12.@p2
.... Inner join @tbl20 on p.@p1 = tbl20.@p1 and p.@p2 = tb120.@p2

Then change your code - and use some more accurate, descriptive variable names to boot. You need to build the query you want table-by-table in your loop rather than generate a single select statement (which you only print). Inside the loop you concatenate the table name and the join clause to the actual query you want (which is initialized with the value you set before the loop). But I suspect this is not actually going to result in a useful query. — SMor, Feb 14 '22 at 18:59

score 1 · Accepted Answer · answered Feb 14 '22 at 19:03

Since you are adding your static beginning string to the variable each time, you are getting your current results. Add another variable to store the moving target, and move the static bit out of the WHILE loop.

    DECLARE @max int = 20
    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableName VARCHAR(50)
    DECLARE @id int = 1 
    Declare @Param1 varchar(20) ='p1', @param2 varchar(20) ='p2'
    DECLARE @qry NVARCHAR(MAX)
    DECLARE @SQL2 VARCHAR(MAX) = ' '
    Declare @strcolumns nvarchar(max) = 'select c1, c2, c3, ...c30 from primarytable '  
---------------  
select @max = MAX(Id) from @Table
WHILE (@id <= @max)
BEGIN
SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =      'INNER JOIN  '+ @TableName + ' ON p.' + @Param1 + ' = ' + @TableName + '.'+ @param1 + ' AND p.' +  @Param2 + ' = ' + @TableName + '.'+ @param2 + ' '
SET @id = @id +1
SET @SQL2 = @SQL2 + @SQL
END 
SET @strcolumns = @strcolumns + @SQL2
PRINT (@strcolumns)

Dynamically looping multiple tables to inner join clause

1 Answers1