SQL Server - Error when using % in subquery

gen3exo

I have a query which contains

AND E.escalation_ID IN (CASE WHEN ISNULL(@template,0) =0
THEN (E.Escalation_ID)
ELSE (SELECT escalation_ID FROM template_info_TB 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?

Nov 18 '08 #1

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.

Nov 19 '08 #2

gen3exo

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.

Nov 25 '08 #3