using comma separated values without dynamic Sql

Question

Post reply

using comma separated values without dynamic Sql

dlukic

Newbie

Points: 1
More actions
January 6, 2005 at 2:21 pm

#84508

I think that i have seen on this foum a post that explains how to select using comma separated values in the Select IN statment
-------------------------------------
eg:
declare @values varchar(100)
set @values = '1,2,3,4'
select * form table where col1 in (@values)
---------------------------------------------
without using dyanmic sql. does anybody know where to find this script or konws how to do that.
thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

The technique goes something like this:

declare @values varchar(100)

-- note the change with the addition of commas

set @values = ',1,2,3,4,'

select *

from table

where charindex(',' + col1 + ',', @values) > 0

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 2

But to do without dynamic sql you need to create a storage area using a temp table or table variable and put each item into it's own row. Plenty of code in the forums for this just do a search on WHILE should turn up a few. Then it is a matter of either join the table item to the main table or use a select statement for the table in the IN clause.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

See, if this helps:

http://www.sommarskog.se/arrays-in-sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

vadba SSChampion Points: 11133 More actions · Answer 4

Here's a link to a previous question in which I posted some code I use.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=143488#bm143900