sending in a comma delimted string to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sending in a comma delimted string to SQL

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-10-10 : 13:42:09

Basically I am trying to figure out how I build a string of theNames from theTable. Then I need to pass that string of Names from theTable into another query that will return the IDs. Below is the queries I have written and the results. You can see that the result of @stringIDs is 'test1','test2','test3','test4'. However, when I pass in the variable, I get no results. But when I pass in the actual string, I get results. What am I missing? Thanks!!


DECLARE @stringIDs varchar(100)

SELECT @stringIDs = ('''' + REPLACE([theName],'/',''',''') + '''') FROM theTable WHERE id = 147

SELECT @stringIDs
-- The Result here is 'test1','test2','test3','test4'

SELECT id 
FROM theTable 
WHERE theName IN (@stringIDs)
--No Result from this query

SELECT id 
FROM theTable 
WHERE theName IN ('test1','test2','test3','test4')
--But this query return four rows
id
1
2
3
4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 13:50:53

[code]DECLARE @stringIDs varchar(8000)

SELECT @stringIDs = LEFT(vl.vallist,LEN(vl.vallist)-1)
FROM (SELECT REPLACE([theName],'/',''',''') + ','
FROM theTable
WHERE id = 147
FOR XML PATH(''))vl(vallist)

SELECT id
FROM theTable
WHERE ','+@stringIDs+',' LIKE '%,'+theName +',%'[/code]

Harry C
Posting Yak Master

148 Posts

Posted - 2008-10-10 : 14:13:59

That did not work for me. I still get no results...Thanks

If I do a SELECT @stringIDs I get

test1','test2','test3','test4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 14:29:53

how are values present in theName column?

Harry C
Posting Yak Master

148 Posts

Posted - 2008-10-10 : 15:31:08

quote:
Originally posted by visakh16

how are values present in theName column?

They are just varchars

theName
test1
test2
test3
test4

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 17:11:32

exec(N'
SELECT id
FROM #theTable
WHERE theName IN ('+@stringIDs+')')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 01:12:10

quote:
Originally posted by Harry C

quote:
Originally posted by visakh16

how are values present in theName column?

They are just varchars

theName
test1
test2
test3
test4

then this will be enough

DECLARE @stringIDs varchar(8000)

SELECT @stringIDs = LEFT(vl.vallist,LEN(vl.vallist)-1)
FROM (SELECT [theName] + ','
      FROM theTable 
      WHERE id = 147
      FOR XML PATH(''))vl(vallist)

SELECT id 
FROM theTable 
WHERE ','+@stringIDs+',' LIKE '%,'+theName +',%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 04:09:20

Also search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources