We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Subquery returned more than 1 value.

danburyit
danburyit asked
on
Medium Priority
496 Views
Last Modified: 2012-06-21
Here's another Where Clause question.

AND a.AccountID IN (COALESCE(@AccountID, (SELECT Items FROM dbo.SplitIDs(@AccountIDs)), a.AccountID))

This line does not work because you can't use a SELECT query in an expression that returns more than one value. The problem is that a single ID integer value may be passed in, or a string of several comma separated IDs may be passed (which is split by a UDF that returns a table of integers), or possibly no ID is passed in at all, in which case we only want to search on other possible parameters and not have to worry about the ID field at all. Can this be done in a single procedure or do I just need to write two procedures, one for passing in integers and one for passing in strings? (One procedure would just make for easier maintenance.)

Thanks

John
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I suspect the problem is that COALESCE can't handle a table result in its input.

Remove the COALESCE and make sure another way that NULLs do not get included:


AND (@AccountIDs IS NULL OR a.AccountID IN (SELECT Items FROM dbo.SplitIDs(@AccountIDs) WHERE Items IS NOT NULL))

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Ved Prakash AgrawalSenior Manager, Database Engineering

Commented:
Hi Coalesce is used to give the value which get first not null.
and it can accept only comma seprated values But your Select Queries return without comma seprated values.


AND a.AccountID IN (COALESCE(@AccountID, (SELECT top 1 Items FROM dbo.SplitIDs(@AccountIDs)), a.AccountID))

if you can use this then it wil work.

Author

Commented:
I am aware that COALESCE will not work. Our friendly SQL Parser doesn't like subqueries that return more than one value used as an expression. Using only the top 1 is not an acceptable solution. What I have in my query is the possibility that a single ID will be passed to the query in the @AccountID parameter, or that several IDs will be passed in the @AccountIDs parameter, or that no value will be passed for either of those two parameters. While we all can agree that the COALESCE example I included in my original post will not work, it does illustrate the logic of what I need to accomplish: AccountID = @AccountID OR AccountID IN @AccountIDs OR don't filter anything on the AccountID field. Hope this clarifies.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yes, and I believe the version I posted does that.

Author

Commented:
AND (@AccountIDs IS NULL OR a.AccountID IN (SELECT Items FROM dbo.SplitIDs(@AccountIDs) WHERE Items IS NOT NULL))

This phrase works only using the @AccountIDs variable, but neglects to consider the @AccountID variable that passes in a single integer. But that's actually OK because even if I'm only passing in a single account ID, I can still use the @AccountIDs string variable and do away with the @AccountID integer variable altogether. The extra overhead of calling the SplitIDs function on a string with just one account id is pretty minimal. So in the end, I can pass in one ID, a comma separated list of IDs or no ID at all and the query works as it should. Kudos for Mr. Pletcher.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry, you're right, I actually didn't even notice that there were separate variables for single and multiple ids :blush: .
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.