SQL Server - Error when using % in subquery

[gen3exo]

I have a query which contains

AND E.escalation_ID IN (CASE WHEN ISNULL(@templat e,0) =0
THEN (E.Escalation_I D)
ELSE (SELECT escalation_ID FROM template_info_T B WHERE field_name LIKE '%'+@template+' %')
END

This throws the multiple results error

If the last line is changed to LIKE 'g', the query doesn't throw the error.

I am using the '%'+@variable+' %' in a regular query and have no problem at all.

I can't use EXISTS because I need an "IN" list.

Is this a known bug in SQL Server 2005?

[Delerna]

From what you describe I suggest that It's not a bug. That is how it should work.

It appears that you are returning multiple records where the query is expecting a single record.
There is not enough of the whole query for me to tell you where, it is, but that is what the error message you are getting means.

[gen3exo]

Subqueries can not be used as expressions

IN SQL SERVER, subqueries can not be used as expressions in a case statement. Normally, the "IN" would expect more than one record to be returned.