You must Sign In to post a response.
  • Category: SQL Server

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


Sign In to post your comments