468,316 Members | 2,041 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

SQL Server - Error when using % in subquery

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
2 1614
Delerna
1,134 Expert 1GB
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
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Alexander DEJANOVSKI | last post: by
11 posts views Thread by David Berry | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
4 posts views Thread by eliffman | last post: by
4 posts views Thread by solidsna2 | last post: by
10 posts views Thread by Shals | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.