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

I'm still not sure that I understand. But it seems that you are making things unnecessarily difficult. Or there is something you have yet to tell us...

Anyway, from what you have said this far, scrap that dummy value and just let the table variable be empty when the user makes no choices. Then the query can be:

SELECT cd.CedingID, *
FROM dbo.expectation e
LEFT JOIN @tblCeding cd ON cd.CedingID=e.complying_fund_id

You will get back all rows in dbo.expectation, and if there is a match in the multi-select thing, you will also get values in cd.CedingID. If there are multiple matches in @tblCedeing, the rows in dbo.expectation will be duplicated.

But I am not really sure that I understand what you want to achieve with your query. It could help if you post CREATE TABLE statements for your table (simplified to focus at the problem), INSERT statements with sample data, and the desired result given the sample. Finally, a brief explanation why you want that result.