Dynamic query Join

Question

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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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