T SQL Conditional join based on parameter value

Question

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

Solution

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