Array Question

Question

Array Question

Aggie D

SSC-Addicted

Points: 443
More actions
February 26, 2004 at 7:35 am

#84042

I am not even sure if this is possible, but I am trying to pass a string of Comma delimented integers and would like to do an "IN" select on them. Is this even possible. Here is my test code that obviously does not work. If anyone has any ideas please let me know.
declare @t table (
tid int,
tname varchar(5)
)
INSERT INTO @T (tid, tname) VALUES (1,'HI')
INSERT INTO @T (tid, tname) VALUES (2,'Ho')
INSERT INTO @T (tid, tname) VALUES (3,'HI')
INSERT INTO @T (tid, tname) VALUES (4,'Ha')
INSERT INTO @T (tid, tname) VALUES (5,'Hu')
DECLARE @j-2 VARCHAR(20)
SET @j-2 = '1,3,4'
SELECT * FROM @t where tid IN @j-2

Viewing 12 posts - 1 through 11 (of 11 total)

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

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

you will have to use dynamic SQL for your SELECT statement or parse the string and save all the values in a temp table and then use the temp table in the subselect.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Aggie D SSC-Addicted Points: 443 More actions · Answer 2

Thanks for the info. I have used the subquery with another table before, but I was just seeing if this was possible.

Tatsu SSCertifiable Points: 7824 More actions · Answer 3

If you're using SQL 2000, you would want to use a table variable rather than a temporary table for performance reasons (See BOL for details). You may also want to consider created a user defined function that parses the input string and returns a table variable with the results. You can even join this in your outer query if you need to. Best of all it makes your query easier to read/maintain and it is reusable for other queries.

[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog

noeld SSC Guru Points: 96590 More actions · Answer 4

declare @t table (

tid int,

tname varchar(5)

)

INSERT INTO @T (tid, tname) VALUES (1,'HI')

INSERT INTO @T (tid, tname) VALUES (2,'Ho')

INSERT INTO @T (tid, tname) VALUES (3,'HI')

INSERT INTO @T (tid, tname) VALUES (4,'Ha')

INSERT INTO @T (tid, tname) VALUES (5,'Hu')

DECLARE @j-2 VARCHAR(20)

SET @j-2 = '1,3,4'

SELECT * FROM @t

WHERE CHARINDEX(',' + Convert(varchar, tId) + ',', ','+@j+',') > 0

* Noel

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

As has been said above, there are no arrays in SQL Server available. However you might get some ideas here 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]

Robert W Marda SSChampion Points: 13413 More actions · Answer 6

Nice solution noeld. Before now I never saw that before. Thanks!

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

noeld SSC Guru Points: 96590 More actions · Answer 7

Frank's link is one of the most complete descriptions I have seen for this kind of opperations

* Noel

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

Yes, some of the finest and smartest SQL guys around have given their input to that article.

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

noeld SSC Guru Points: 96590 More actions · Answer 9

Yep, Like every great thing, is based on the hard effort of a lot of people

* Noel

Steve Croll SSC-Addicted Points: 488 More actions · Answer 10

I've used this before:

http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html

Works like a charm.

Aggie D SSC-Addicted Points: 443 More actions · Answer 11

Sorry to take so long to post back, I have Work up to my ear. (Who hasn't). All I have to say is that noeld had to have the best soln, I have seen with this. I have to agree, I have never seen that before. Thanks all for the help.