SQL query that takes an array of numbers

Hi,

I need a SQL query that takes an array of numbers such as (see query below)
How can I do use an array of different numbers in a query???

Thank you!

 public DataTable MyQuery(string x, string y, array of numbers)
        {
            string sql = "SELECT x,y, array";
            SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

Open in new window

Rad1Asked:
Who is Participating?
 
RouchieCommented:
You can pass the array as an string, e.g. "1,5,6,7" and then using a SQL function to split it into a table.  There are loads of examples here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Here's a very quick one.  You might need to add detection for empty values

DECLARE @separator char(1)
SET @separator = ','

DECLARE @array nvarchar(max)
SET @array = '1,5,7,56,4,5,7,4564,45,5456,4,65,456,4'

DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)

DECLARE @tbl TABLE(ID INT IDENTITY(1,1), dateAttained INT)

WHILE patindex('%' + @separator + '%', @array) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @array)
		SELECT @array_value = LEFT(@array, @separator_position - 1)
		INSERT INTO @tbl(dateAttained) VALUES (@array_value)
		SELECT @array = stuff(@array, 1, @separator_position, '')
	END

SELECT * FROM @tbl 

Open in new window

0
 
DcpKingCommented:
Relational databases don't normally admit of "arrays" as such - they're called "tables" :) Can you explain what you're trying to achieve, and maybe we can come up with a good way of getting from where you are to there.
0
 
Rad1Author Commented:
Excellent!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.