SQL query that takes an array of numbers

Posted on 2012-08-24
Last Modified: 2012-08-25

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();
            return dt;

Open in new window

Question by:Rad1
    LVL 16

    Expert Comment

    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.
    LVL 25

    Accepted Solution

    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:

    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
    		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, '')
    SELECT * FROM @tbl 

    Open in new window


    Author Closing Comment


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now