Category: SQL Server

26 Feb 2017 17:11

Joined:	06/06/2011
Level:	Gold
Points:	1173

Points:	5

Pass the column dynamically and combine to single query

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.

#768945

27 Feb 2017 10:50

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi lily,

As per my understanding you want dynamic sql query right?

Use below sample to achieve your thing


DECLARE @ISBN varchar(20) = 'a,bbbb,ccc,ddd,ee' 

SET @ISBN='SELECT ' +@ISBN+ ' from yourtablename';

Execute @ISBN;

you have to remember before execute you have to recheck the query by using PRINT statement, the print statement query should be execute without any issues then only your query is correct.

Hope this helps you....

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#768947

27 Feb 2017 11:50

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	4

Hi,

I feel you need to make this one as a Stored Procedure and that will help you to solve your problem.
Dynamic query in select format will be performance degrader. You can try the following procedure and make the changes as per your requirement.



Create PROCEDURE [dbo].[USP_DynCol ]                                              
   (                                
          @MyColumns AS NVARCHAR(MAX)     = ''      
 )                                                        
AS                                                                
BEGIN                                                 
 DECLARE    @SQLquery  AS NVARCHAR(MAX)

set @SQLquery = N'SELECT  * FROM Table1 Where '+ @MyColumns +' NOT in (Select '+@MyColumns+'  from Table2)
            
exec sp_executesql @SQLquery;
    END

Thanks,
Mani

Pass the column dynamically and combine to single query

About dotnetspider.com

Quick Links

General

Services