Pass column names dynamically and combine into single query [Resolved]

CREATE TABLE #Table1 (Item NVARCHAR(50),Price NVARCHAR(50),Quantity NVARCHAR(50),Product NVARCHAR(50),Description NVARCHAR(50))

INSERT INTO #Table1 VALUES ('Item1','Price1','Quantity1','Product1','Description1'),('Item2','Price2','Quantity2','Product2','Description2'),('Item3','Price3','Quantity3','Product3','Description3')

CREATE TABLE #Table2 (Item NVARCHAR(50),Price NVARCHAR(50),Quantity NVARCHAR(50),Product NVARCHAR(50),Description NVARCHAR(50))

INSERT INTO #Table2 VALUES ('Item1','Price1','Quantity1','Product1','Description1'),('Item2','Price2','Quantity2','Product2','Description5'),('Item4','Price4','Quantity4','Product4','Description4')



DECLARE 

		@sql NVARCHAR(MAX),  

        @paramlist  NVARCHAR(4000), 

		@a NVARCHAR(MAX),

		@sep NVARCHAR(MAX),

		@finalQueries NVARCHAR(MAX),

        @nl CHAR(2) = CHAR(13) + CHAR(10)



SET @a = 'Item,Price,Quantity,Product,Description'

SET @sep = ','



SELECT @sql = '



DECLARE @ColumnNameTbls TABLE (ColumnNames NVARCHAR(50))



DECLARE @QueryTbls TABLE (Query NVARCHAR(MAX))

INSERT INTO @QueryTbls 

VALUES (''SELECT * FROM #Table1 WHERE [PLACEHOLDER] NOT IN (SELECT [PLACEHOLDER] FROM #Table2)'')



;WITH a AS(

		SELECT CAST(0 AS BIGINT) AS idx1,CHARINDEX(@sep,@a) idx2

		UNION ALL

		SELECT idx2+1,CHARINDEX(@sep,@a,idx2+1)

		FROM a

		WHERE idx2>0

	)

	INSERT INTO @ColumnNameTbls

	SELECT SUBSTRING(@a,idx1,COALESCE(NULLIF(idx2,0),LEN(@a)+1)-idx1) AS ColumnName

	FROM a

	'  + @nl



SELECT @sql += '	



		--Build the queries to be executed

		SELECT 	

			@finalQueries = STUFF((SELECT '' UNION ALL '' + 

							X.FinalQueries + '' '' 

							FROM (SELECT DISTINCT

							FinalQueries = REPLACE(q.Query,''[PLACEHOLDER]'',c.ColumnNames)

							FROM @QueryTbls q,@ColumnNameTbls c) X       

						FOR XML PATH('''')),1,10,'''')



		' + @nl



print @sql



SELECT @sql += ' 

				--Execute the Query

				EXEC (@finalQueries) ' + @nl



SELECT @paramlist = '

				@a NVARCHAR(MAX),

				@sep NVARCHAR(MAX),

				@finalQueries NVARCHAR(MAX)'

					

EXEC sp_executesql 

					@sql

				,@paramlist

				,@a

				,@sep

				,@finalQueries





 DROP TABLE #Table1

 DROP TABLE #Table2



/*



Item	Price	Quantity	Product	Description

Item2	Price2	Quantity2	Product2	Description2

Item3	Price3	Quantity3	Product3	Description3

Item3	Price3	Quantity3	Product3	Description3

Item3	Price3	Quantity3	Product3	Description3

Item3	Price3	Quantity3	Product3	Description3

Item3	Price3	Quantity3	Product3	Description3



*/

--
Thanks & Regards,
RNA Team

--Variable to hold the paramter to passed in 
DECLARE @Param Varchar(50)
--Set value to paramter
SET @Param = 'Item'
--Variable to hold dynamic query
DECLARE @SQL varchar(1000)
--Creating dynamic query starts here
SET @SQL = 'select * from Table1 '
--Check if parameter is having any values
IF @Param IS NOT NULL 
--If so then add a where condition to query
 SET @SQL = @SQL + 'WHERE '
--Check if parameter is having any values
IF @Param IS NOT NULL
 SET @SQL = @SQL + @Param +' NOT IN ( select ' + @Param +' from Table2)'
--Execute the sql
EXEC(@SQL)

Thanks,
A2H
My Blog