Subquery returned more than 1 value.

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.)


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
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.
danburyitAuthor 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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
Yes, and I believe the version I posted does that.
danburyitAuthor 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 DBACommented:
Sorry, you're right, I actually didn't even notice that there were separate variables for single and multiple ids :blush: .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.