[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL query that takes an array of numbers

Posted on 2012-08-24
Medium Priority
829 Views
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";
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
``````
0

LVL 16

Expert Comment

ID: 38331870
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

LVL 25

Accepted Solution

Rouchie earned 2000 total points
ID: 38332030
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
``````
0

Author Closing Comment

ID: 38332216
Excellent!!!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month18 days, 23 hours left to enroll