SQl server Dynamic query

Question

I have a table Connection

KEY    Base_TBL   Connected_table     Base_tbl_colmn   Connected_table_colmn
----   ------       -------------     ------------      ------------------
PRM    Table1      Table2,Table3       colm1                 Colm2,colm3
FRN    Table4        table5            colm4                    colm5

I need to generate a dynamic Query which should give an output something like this

select * from table1 
INNER JOIN Table 2
ON table1.colm1 =table2.colm2
INNER JOIN Table3 
on Table1.colm1=tabl3.colm3

number of tables in Connected_table can be anything and i have to join on the basis of that.

I have tried REPLACE Function within The dynamic query but not getting the desired result.

CREATE PROCEDURE [dbo].jointables 
[Key] nvarchar(10)
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 0


DECLARE @sql as nvarchar(4000)

select @sql= 'select * from '+ Base_TBL + 'inner join ' +

Please guide me how to proceed further

If you can still change your table design, redesign it to not have columns that contain comma-seperated lists. Please refer to this [SO thread](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) for why this is a bad idea. — TT., Nov 24 '15 at 07:46
I know this is a bad design but its not possible for me to change the Table design, I have to proceed with this. — TheSQLDecoder, Nov 24 '15 at 07:50
StackOverflow is filled with questions very similar to yours, and the corresponding answers. The first I googled: [here](https://stackoverflow.com/questions/18952379/need-a-qry-to-join-a-comma-separated-column-with-another-table-with-its-id-in-it). — TT., Nov 24 '15 at 07:52
Aaron Bertrand wrote a comparison of [all the available methods[(http://sqlperformance.com/2012/07/t-sql-queries/split-strings) in 2012. — Panagiotis Kanavos, Nov 24 '15 at 10:09

Nolan Shang · Accepted Answer · 2015-11-24T10:08:48.627

CREATE FUNCTION [dbo].[FN_SPLIT](@Long_str varchar(max),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID      inT IDENTITY PRIMARY KEY,      
    SPLIT   varchar(max)    
)    
AS   
BEGIN 
        DECLARE @sxml XML

        SET @sxml='<root><node>'+REPLACE(@Long_str,@split_str,'</node><node>')+'</node></root>'
        INSERT INTO @tmp([SPLIT])
        SELECT b.value('.','varchar(max)') FROM @sxml.nodes('root/node') AS s(b)


    RETURN     
END 
GO

DECLARE @sql as nvarchar(4000)
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
;WITH testdata AS 
(
select 'PRM' AS [Key],'Table1' AS Base_TBL,'Table2,Table3' AS  Connected_table,'colm1' AS Base_tbl_colmn,'Colm2,colm3' AS Connected_table_colmn
)

SELECT * INTO #temp FROM testdata

select @sql= 'select * from '+ Base_TBL +' ' +t.joinstring
FROM #temp
CROSS APPLY (
    SELECT STUFF((
 SELECT ' INNER JOIN '+ ft.[SPLIT] + ' ON ' + Base_TBL+'.'+Base_tbl_colmn +'='+ft.[SPLIT]+'.'+fc.[SPLIT]
FROM 
dbo.FN_SPLIT(Connected_table,',') AS ft 
INNER JOIN dbo.FN_SPLIT(Connected_table_colmn,',') AS fc ON fc.ID=ft.ID
FOR XML PATH('')),1,1,'') AS joinstring
) AS t
SELECT @sql

Below example just process one line when process multiple lines, you can use another stuff function combine multiple lines

SELECT STUFF((
        select  '  select * from '+ Base_TBL +' ' +t.joinstring 
        FROM #temp
        CROSS APPLY (
            SELECT STUFF((
         SELECT ' INNER JOIN '+ ft.[SPLIT] + ' ON ' + Base_TBL+'.'+Base_tbl_colmn +'='+ft.[SPLIT]+'.'+fc.[SPLIT]
        FROM 
        dbo.FN_SPLIT(Connected_table,',') AS ft 
        INNER JOIN dbo.FN_SPLIT(Connected_table_colmn,',') AS fc ON fc.ID=ft.ID
        FOR XML PATH('')),1,1,'') AS joinstring
        ) AS t FOR XML PATH('')
),1,1,'')

There are a *lot* of duplicate questions that show how to split a comma-separated value efficiently. Using a temporary table or dynamic queries is both inefficient and error-prone. Aaron Bertrand even wrote [a comparision of the available methods](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) in 2012 — Panagiotis Kanavos, Nov 24 '15 at 10:06

SQl server Dynamic query

1 Answers1