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

Posted by Oswaldlily under Sql Server on 2/26/2017 | Points: 10 | Views : 290 | Status : [Member] | Replies : 2
declare @a= Item,Price,Quantity,Product,Description
select @a

select * from Table1 where Item not in (select Item from Table2)
select * from Table1 where Price not in (select Price from Table2)
select * from Table1 where Quantity not in (select Quantity from Table2)
select * from Table1 where Product not in (select Product from Table2)
select * from Table1 where Description not in (select Description from Table2)

Expected Query (Column name has to pass dynamically as)

select * from Table1 where @a not in (select @a from Table2)
=> I have listed with 5 select query. Please tell the possible way to write in single query.




Responses

Posted by: Rajnilari2015 on: 2/27/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Oswaldlily Sir, try the below

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

*/


Note:~ the temporary tables are created only for testing and not part of the real query

--
Thanks & Regards,
RNA Team

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 2/26/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
You could dynamically generate the query with where condition like given below


--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)


Sample Demo : http://sqlfiddle.com/#!3/5dccc/1

Thanks,
A2H
My Blog

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response