0

I am trying to fetch a number of values from a table and store it in a single variable as comma separated values so that I can use that variable as parameter in a different select statement.

Here's the SQL Query:

DECLARE @AccCode VARCHAR(MAX)
SELECT @AccCode = AccCode FROM Account WHERE (*some condition*) --returns more than 1 value
SELECT * FROM AccountSet WHERE AccCode IN (@AccCode)

The problem is that all the values returned from the first select query is not being stored in the @AccCode variable. Instead only the first returned value is being stored.

I am not sure if I'm missing something. Any help would be appreciated.

Thanks in advance..!!

Jagath
  • 51
  • 1
  • 7
  • Even when correcting the second statement to store all the values as a delimited string, the third statement is not a valid option. – Luis Cazares Apr 03 '19 at 17:37
  • @Luis If the second statement returns value as comma delimited string, the third statement should return the result back right? If the second statement returns '1,2,3' as result, the third statement would be 'SELECT * FROM AccountSet WHERE AccCode IN ('1,'2','3')'. Please correct me if I'm wrong.. – Jagath Apr 05 '19 at 10:32
  • No, the third statement would be `SELECT * FROM AccountSet WHERE AccCode IN ('1,2,3')` which is a single value. To use a delimited string as an array of values you need to use a string splitter or the bad option of dynamic sql. You could also change your approach and use a table variable to store the values instead of using a string variable. – Luis Cazares Apr 05 '19 at 13:31

2 Answers2

2

You can use COALESCE:

DECLARE @AccCode VARCHAR(MAX)
SELECT @AccCode = COALESCE(@AccCode + ',' + AccCode, AccCode) FROM Account WHERE (*some condition*) --returns more than 1 value
SELECT * FROM AccountSet WHERE AccCode IN (@AccCode)
Hasan Mahmood
  • 978
  • 7
  • 10
0

You can use FOR XML PATH() clause :

SELECT @AccCode = STUFF( (SELECT ', '+ AccCode 
                          FROM Account 
                          WHERE (*some condition*) 
                          FOR XML PATH('')
                         ), 1, 1, ''
                       )
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • How to get the same result with values in 'single' quotes and separated with commas? I tried this but I am not able to delete the comma from the last position and keep the single quotes which is in the first position. _SELECT @AccCode = STUFF( (SELECT ''''+ AccCode + '''' + ',' FROM Account WHERE (*some condition*) FOR XML PATH('')), 1, 1, '')_ May be I can find the length of the returned statement and then use it as a parameter for STUFF but is there any other simpler way to do this? – Jagath Apr 04 '19 at 04:47