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>1 AND b.name = a.val) OR (@inputCount<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.