Using comma separated integer as string in where clause inside sub query

Question

I have a stored procedure in within which a column having comma separated values (codeid) like ('20045,20069,20079') is being used for where in clause inside sub query for a main select statement. I tried some work around but couldn't find proper solution.

This is my code:

    SELECT 
        CodeID, Name,
        Experience,
        (SELECT COALESCE(@CodeDescription + '', '', '''') + CAST([Description] AS varchar(100)) 
         FROM SynCode 
         WHERE CodeID IN (SELECT * 
                          FROM dbo.fnSplitString(codeid, ','))) AS [codeDescription]
    FROM   
        Code
WHERE CodeIdentity = 1

Here SynCode contains description for each code and dnSplitString is a function which converts the CSV integer string into table.

While executing the above statement I'm getting the following error:

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

Any help on this would be appreciated.

Since I need only one row to return I'm using COALESCE to separate values with comma

Edit: I have

    CodeID as '20045,20069,20079'
    and Iam Expecinting row result as
20045,20069,20079|Marry|2 years|Finance, Retail, Sales

Edit: CodeID, Name, Experience is VARCHAR, and CodeID in SynCode is Int

The message here couldn't be more clear. Your subquery returned more than 1 row. That would be the column you have there. BTW, a subquery as a column is almost always a bad idea. You should use a join instead. This is yet another reason why it is a bad idea to store delimited strings like this. If your data was properly normalized there would not be an issue here at all. — Sean Lange
– Sean Lange, Commented Sep 6, 2017 at 19:45
You'll either need to split the array into a table of values (preferable) or use dynamic sql. — Jason A. Long
– Jason A. Long, Commented Sep 6, 2017 at 19:50
Nope...If you have two rows in SynCode because codeid has two values you will get two rows back. That select statement does NOT generate a csv, on the contrary it is splitting a csv into rows. Hence the error message. — Sean Lange
– Sean Lange, Commented Sep 6, 2017 at 19:53
I'm not sure what do you expect to get from the sub query, so it's kinda hard to give an answer. — Zohar Peled
– Zohar Peled, Commented Sep 6, 2017 at 20:00

sdsc81 · Accepted Answer · 2017-09-08 15:07:16Z

0

The problem was that

when the subquery is used as an expression.

You can't put the query in the Selectstatement because it return more than one element.

Try the following:

SELECT 
    CodeID, Name,
    Experience,
    (SELECT STRING_AGG(Description, ', ') 
     FROM SynCode 
     WHERE CodeID IN (SELECT * 
                      FROM dbo.fnSplitString(codeid, ','))) AS [codeDescription]
FROM   
    Code
WHERE CodeIdentity = 1

Let me know if it works.

Cheers.

edited Sep 8, 2017 at 15:07

answered Sep 6, 2017 at 20:59

sdsc81

5908 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hogan Over a year ago

I'm sure the OP does not want a cross join as a solution. at least join on codeid.

Collectives™ on Stack Overflow

Using comma separated integer as string in where clause inside sub query

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related