CODEWITHSUNDEEP
sqlsql-servert-sql
user2232273
user2232273

Reputation: 4964

T-SQL IN Clause Combined With Case Statement

i have query where i have a in clause combined with a case statement. the error i get is this:

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

i receive in stored procedure a concatenate varchar value like this '2,3,4,5' and i want in my query to filter the ids using the in clause but something i missing

someone can give me a hand with this pls?

@ids varchar(50) = '2,3,4'

DECLARE @mylist TABLE (Id int)
INSERT INTO @mylist
select CONVERT(INT,v) from [dbo].[SplitString](@cardtypes)

//result of @mylist
//-------------------
//2
//3
//4

and then in the query

select * from mytable
where
ctable.Id IN (CASE 
                WHEN ISNULL(@ids,'') <> '' THEN (select id from @mylist) 
                ELSE ctable.Id 
              END)

Note: ctable.Id (INT)

If i have only passing one value it works but if there are more it breaks

thanks in advance

Upvotes: 1

Views: 176

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Out of curiosity, why are you using a temporary table? You can express this as a CTE:

with values as (
      select CONVERT(INT, v)
      from [dbo].[SplitString](@cardtypes)
     )
select t.*
from <table> t
where t.id in (select v from values) or
      not exists (select 1 from values);

Upvotes: 3

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

You're trying to mix scalar value ctable.Id and some resultset select id from @mylist here in one case statement.

This can't be done, but you can rewrite your query like this:

select * from mytable
where
    (isnull(@ids,'') <> '' and ctable.Id in (select id from @mylist))
    or isnull(@ids,'') = ''

Upvotes: 4

Related Questions