Dynamically looping multiple tables to inner join clause-CodePudding

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

CodePudding user response：

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)