CODEWITHSUNDEEP
t-sqlfunctionstored-proceduresjoinconditional-statements
Lazy Coder
Lazy Coder

Reputation: 1217

T SQL Conditional join based on parameter value

I have the need to have a inner join based on the value of a parameter I have in a stored procedure. I'm also using a function to split values out of a string of comma separated values. My code is as follows

Select *
from view_Project as vp
join inline_split_me(@earmark) as e on (vp.EarmarkId LIKE e.Value and @earmark IS NOT NULL)

If @earmark is NULL then I don't want this join to happen at all, otherwise if I have a string of '%' or '119' or '119,120,121' this join should happen and does yield the proper results. I would just like to have it not happen at all if @earmark is null, I thought that I could just use the and @earmark is not null to delineate that however it is not returning the proper results, which is discovered by commenting the join line out and running the same sproc with null as the @earmark param, which gives me all rows as a result. When I keep this join and pass null I get no rows, I've been fiddling with this for some time, any help would be appreciated.

Here is the FUNCTION:

[inline_split_me](@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
    substring(@param, Number,
        charindex(N',' COLLATE SQL_Latin1_General_CP1_CI_AS,
        @param + convert(nvarchar(MAX), N','),
    Number) -
Number)
))) AS Value
   FROM   APM_Numbers
   WHERE  Number <= convert(int, len(@param))
     AND  substring(convert(nvarchar(MAX), N',') + @param, Number, 1) =
                    N',' COLLATE SQL_Latin1_General_CP1_CI_AS)

Got it, thanks Cade Roux and others

if (@earmark = '%')
select *
    from view_Project as vp
    where vp.EarmarkId like @earmark
else
select *
    from view_Project as vp
    where @earmark is null or vp.EarmarkId in (select Value from inline_split_me(@earmark))

Upvotes: 2

Views: 12106

Answers (3)

ABrown78
ABrown78

Reputation: 29

I know this question is pretty old, but I was researching a similar issue and came across this and came up with a totally different solution that worked like a charm.

Use a LEFT JOIN, but then have a filter in you WHERE clause that if your parameter is not null, neither can your join match be null. That functionally results in a conditional INNER JOIN.

SELECT *
FROM 
  A 
  LEFT JOIN B 
    ON A.KEY = B.KEY
WHERE 
  (@JOIN_B IS NOT NULL AND B.KEY IS NOT NULL)
  OR @JOIN_B IS NULL

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89711

INNER JOIN is your problem. A LEFT JOIN will always return the rows on the LEFT, even though when @earmark is NULL, the join condition can never be true.

Select *
from view_Project as vp
LEFT join inline_split_me(@earmark) as e on (vp.EarmarkId LIKE e.Value and @earmark IS NOT NULL)

You could fool around with a UNION to manufacture rows to join when @earmark is NULL

Select *
from view_Project as vp
INNER join (
    SELECT Value, -- columns here ...
    FROM inline_split_me(@earmark) as e
    UNION ALL
    SELECT DISTINCT vp.EarmarkId AS Value, -- NULL, NULL, etc.
    FROM view_Project
    WHERE @earmark IS NULL
) AS e
    ON vp.EarmarkId LIKE e.Value

But frankly, I would just do a conditional logic:

IF @earmark IS NULL
    Select *
    from view_Project as vp
ELSE
    Select *
    from view_Project as vp
    INNER join inline_split_me(@earmark) as e on (vp.EarmarkId LIKE e.Value and @earmark IS NOT NULL)

If you can get away from LIKE:

Select *
from view_Project as vp
WHERE @earmark IS NULL OR vp.EarmarkId IN (
    SELECT Value FROM inline_split_me(@earmark)
)

Upvotes: 4

Philip Kelley
Philip Kelley

Reputation: 40359

...as vp join lined_split_me(@earmark) as... should be defaulting to an inner join, which means that the query only returns rows if matches are found between the two tables. (Double-check by explicitly saying inner join.)

Does the function call return no (zero) rows if @earmark is null? If so, then there should be no rows returned from the query.

Upvotes: 0

Related Questions