Dynamic query Join

RoyB09 301 Reputation points
2023-01-23T17:20:11.99+00:00

hello

I have a temp table (@MyTable1) that is populated from an SSRS Multi-select parameter. The table is joined back to another table to output from a stored procedure as follows …

 

JOIN @MyTable1 As b ON b.ItemID=v.ItemID

 

In the Multi Select parameter there is a value ‘_NULL’. If this is the only value selected into @MyTable1, I need to change the JOIN to a LEFT JOIN

 

i.e. 

JOIN @MyTable1 As b ON b.ItemID=v.ItemID becomes  

LEFT JOIN @MyTable1 As b ON b.ItemID=v.ItemID

 

Is there a way to achieve this without a dynamic SQL statement?

Thanks in advance

Roy

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,594 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 68,536 Reputation points MVP
    2023-01-25T22:23:44.6933333+00:00

    So if @MyTable is empty, you want all rows, but if there are values, you only want the matches?

    The simple-minded solution would be:

    IF NOT EXISTS (SELECT * FROM @MyTable) 
       SELECT ... FROM dbo.DataTable
    ELSE 
    BEGIN
       SELECT ...
       FROM   dbo.DataTaable dt
       WHERE  dt.item IN (SELECT t.item FROM @MyTable t)
    END
    

    If you have more conditions of this kind, so that it gets impractical with multiple statements, you can do:

    SELECT @hasvalues = IIF(EXISTS (SELECT * FROM @MyTable), 1, 0)
    
    SELECT ...
    FROM   dbo.DataTaable dt
    WHERE  (@hasvalues = 0 OR dt.item IN (SELECT t.item FROM @MyTable t)
    

5 additional answers

Sort by: Most helpful
  1. Tasadduq Burney 7,186 Reputation points MVP
    2023-01-23T17:35:33.1033333+00:00

    It is possible to achieve this without using dynamic SQL, but it would require a bit of extra logic in your stored procedure.

    You can first SELECT the rows from @MyTable1 where the value is not '_NULL', and then UNION that with a SELECT statement that returns all the rows from the other table where the value is '_NULL', and then use a CASE statement to check if the '_NULL' value is present in @MyTable1.

    Based on that you can decide to do either a JOIN or LEFT JOIN.

    Here's an example of how the stored procedure would look like:

    CREATE PROCEDURE MyProcedure 
        (@MyTable1 AS MyTable1 READONLY)
    AS
    BEGIN
        -- Check if '_NULL' value is present in @MyTable1
        DECLARE @IsNullValuePresent BIT = (SELECT COUNT(*) FROM @MyTable1 WHERE ItemID = '_NULL') > 0
        -- Select rows from @MyTable1 where the value is not '_NULL'
        SELECT v.*
        FROM OtherTable v
        -- Use a case statement to decide whether to use JOIN or LEFT JOIN
        CASE @IsNullValuePresent 
            WHEN 1 THEN LEFT JOIN
            ELSE JOIN
        END @MyTable1 b ON b.ItemID = v.ItemID
        WHERE b.ItemID <> '_NULL'
        UNION
        -- Select all rows from the other table where the value is '_NULL'
        SELECT v.*
        FROM OtherTable v
        WHERE NOT EXISTS (SELECT * FROM @MyTable1 WHERE ItemID = '_NULL')
    END
    
    No comments

  2. SQL Worker 0 Reputation points
    2023-01-23T17:41:25.28+00:00

    Hi Roy,

    you may try this:

    Change the JOIN permanently to LEFT JOIN and use the where-clause to decide:
    WHERE (b.ItemID=v.ItemID OR v.ItemID = '_NULL')

    Thomas

    No comments

  3. jose de jesus 136 Reputation points
    2023-01-24T03:17:32.24+00:00

    Like this

    if @param is not null

    Begin

       select block
    

    end

    if @param1 is not null and @param2 is not null

    begin

        Select block
    

    end

    No comments

  4. jose de jesus 136 Reputation points
    2023-01-24T03:20:29.65+00:00
    if @param is not null
    
    begin 
    
       select statement for 1 paramaetr
    
    end
    
    
    if @param is not null and @param2 is not null
    
    begin 
    
       select statement for 2 paramaetr
    
    end
    
    No comments