JOIN on LIKE when input udtt count is > 1 This query accepts a User-Defined Table Type variable and then joins that with a table.  If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input.  This is essentially what I have now, but it's quite slow for larger inputs (testing around 500 takes around 5min).

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )
SELECT b.* FROM @inputUDTT a
INNER JOIN Tabl b ON (@inputCount&gt;1 AND b.name = a.val)
    OR (@inputCount&lt;2 AND b.name like '%' + a.val+ '%')


I've also tried having the SELECT COUNT(*) directly in the join and formatting the condition as a CASE WHEN switch, but neither helped.  I know I could alter this into a dynamic query, but that seems excessive for something that seems like it should be fixable in a simpler method.
I'd start by breaking this out to get a better idea of which part is slow. This should clarify the execution plan overall.

From there you can start sorting out other techniques, like temp tables, recompile hints, indexes, etc.

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )

SELECT b.* 
FROM @inputUDTT a
INNER JOIN Tabl b 
ON (@inputCount &gt; 1 AND b.name = a.val)

UNION ALL       

SELECT b.* 
FROM @inputUDTT a
INNER JOIN Tabl b 
ON (@inputCount &lt; 2 AND b.name like '%' + a.val+ '%')


For example, in the first query where you have an equality predicate, the optimizer can choose different join types (though it may only ever choose nested loops because of the low row estimates for a table variable. In the second query, the only join type available with no equality predicate is nested loops.

Hope this helps!