CODEWITHSUNDEEP
sql
user3813717
user3813717

Reputation: 3

Subquery returned more than 1 value using IN and ISNULL commands

I need help running a SQL query I am getting this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is the query I tried to run:

select * 
from [Group] 
where [Group].Name IN (ISNULL((Select * from Split(@AdGroups)), [Group].Name))

Edit:

If @AdGroups is NULL I need all the rows in the Group table but IF it has any names in it I need rows with only those names.

Upvotes: 0

Views: 758

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Move the isnull() logic into the subquery:

select *
from [Group] g
where g.Name IN (Select coalesce(t.name, g.Name)
                 from Split(@AdGroups)) as t(name)
                )

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

The problem is Select * from Split(@AdGroups) returns more then one row. This subquery is used as an argument to ISNULL. ISNULL only take a single value, checks if it is null, and returns the second value if the first value is null. It can't handle multiple values.

I don't believe you need the is null. 

select * from [Group] where [Group].Name IN ( * from Split(@AdGroups))

If this query doesn't provide the needed results, please show the wrong results and the desired results in an edit to your question.

Upvotes: 1

Related Questions