0

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

R.Sharma
  • 203
  • 2
  • 9
  • 2
    Perhaps you're looking for [FOR XML](https://stackoverflow.com/questions/1564980/building-a-comma-separated-list). – Tyler Roper Sep 06 '17 at 19:45
  • 4
    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 Sep 06 '17 at 19:45
  • You'll either need to split the array into a table of values (preferable) or use dynamic sql. – Jason A. Long Sep 06 '17 at 19:50
  • @SeanLange, Sub query actually returns a single csv row but query inside where in clause returns data in a table(which might be the cause for error). I agree using delimited string is bad idea but this is how data is provided. – R.Sharma Sep 06 '17 at 19:51
  • 2
    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 Sep 06 '17 at 19:53
  • @SeanLange, shouldn't COALESCE address this by converting returned records into csv? – R.Sharma Sep 06 '17 at 19:56
  • Nope. [`Coalesce`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql) simply returns the first argument that is not null. – Zohar Peled Sep 06 '17 at 19:58
  • @R.Sharma The only thing `COALESCE` does is return the first non-`NULL` value. – Siyual Sep 06 '17 at 19:58
  • @ZoharPeled, Right. Any work around you can suggest? – R.Sharma Sep 06 '17 at 19:59
  • @Santi gave you proper hint – avb Sep 06 '17 at 20:00
  • 1
    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 Sep 06 '17 at 20:00
  • Well, I'm expecting a record having description in csv along with csv CodeID – R.Sharma Sep 06 '17 at 20:02
  • @R.Sharma Did you visit the link I provided you in the very first comment? If you're looking for a subquery that generates a comma-separated list, I strongly believe you'll find your answer there. – Tyler Roper Sep 06 '17 at 20:03
  • @Santi, Thanks for the link. I did tried the solution earlier but since the values should be in INT I'm getting conversion error – R.Sharma Sep 06 '17 at 20:12
  • please give example data -- example in the tables example in the parameters and then expected results from your query. THIS IS THE ONLY way we will be able to help you. – Hogan Sep 06 '17 at 20:36
  • @Hogan, Updated in question – R.Sharma Sep 06 '17 at 20:46
  • Most important, put table definitions, or at least wich columns they contain – sdsc81 Sep 06 '17 at 20:53
  • ok but you don't tell us what is in the tables --where does this come from >>>>Marry|2 years|Finance, Retail, Sales<<< for example I could say your database has a table called table with one column and one row with this value and the solution is `select * from table` you need to tell us the data model and data contents of your system in order for us to have a clue how to answer your question to create that output. – Hogan Sep 07 '17 at 05:05
  • Give an example of what dnSplitString( '20045,20069,20079' ) return. In which table is 'Description'. What is the purpouse of COALESE (in particular the third parameter seems useless considering the second is not null). – sdsc81 Sep 08 '17 at 14:29

1 Answers1

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.

sdsc81
  • 570
  • 8
  • 18