title: T SQL Conditional join based on parameter value
body: <p>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</p>
  
  <pre><code>Select *
  from view_Project as vp
  join inline_split_me(@earmark) as e on (vp.EarmarkId LIKE e.Value and @earmark IS NOT NULL)
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is the FUNCTION:</p>
  
  <pre><code>[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 &lt;= convert(int, len(@param))
       AND  substring(convert(nvarchar(MAX), N',') + @param, Number, 1) =
                      N',' COLLATE SQL_Latin1_General_CP1_CI_AS)
  </code></pre>
  
  <p>Got it, thanks Cade Roux and others</p>
  
  <pre><code>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))
  </code></pre>