SQL To Filter A SubSet Of Data

Question

I have an arraylist that holds a subset of names found in my database. I need to write a query to get a count of the people in the arraylist for certain sections i.e. There is a field "City" in my database from the people in the arraylist of names I want to know how many of them live in Chicago, how many live in New York etc.

Can someone help me how I might set up an sql statement to handle this. I think somehow I have to pass the subset of names to sql somehow.

Here is a sample how I am writing my sql in my code

Public Shared Function GetCAData(ByVal employeeName As String) As DataTable
        Dim strQuery As String = "SELECT EMPLID, EMPLNME, DISP_TYPE, BEGIN_DTE FROM Corr WHERE (EMPLNME = @name)"
        Dim cmd As New SqlCommand(strQuery)
        cmd.Parameters.Add("@name", SqlDbType.VarChar)
        cmd.Parameters("@name").Value = employeeName
        Dim dt As DataTable = GenericDataAccess.GetData(cmd)
        Return dt
End Function

I need a way to create a function using your sql statement to return a datatable object of names and the parameters would be city, and the List of names.

The above example isnt the sql I am looking for its just a skeleton of what the function would look like that I want to create.

So then you would use the function by iterating through all the cities passing in the same set of names each time in the fron end.

Answer 1

Your Sql statement will need to look like:

select city, count(*)
from table
where city in ('Chicago', 'New York')
group by city

Where the list of cities is your arraylist. You could pas this into a stored procedure as a variable or you could build the Sql string dynamically within your code.

Answer 2

The best approach would depend on how big the subset of names is.

The very simplest, and probably worst approach would be to just create some dynamic sql like this..

"select city,count(*) from table where user in ("name1","name2","name3") GROUP BY city"

Where the in section is generated from the arraylist, perhaps like the following

private string CreateSQL(List<string> names)
{
    var sb = new StringBuilder();
    sb.Append("select city,count(*) from table where user in ('");
    foreach ( var name in name)
    {
       sb.Append("'");
       sb.Append(name);
       sb.Append("',");
    }
    sb.Remove(sb.Length-1,1); //remove trailing ,
    sb.Append(") GROUP BY city");

    return sb.ToString();
}

The where in clause has a 100 item limit I believe. If your subset is anything like that length, then you really need a better approach, like putting the users in another table and doing a join. In fact if the subset of users is taken from the same database by another query, then post that and we'll right a single query.

edit: I actually don't know how to do where user in type queries as parameterised commands, be wary of sql injection !

Answer 3

One way of doing this is to pass it in as an XML parameter. The XML would be something like

@Cities = '<xml><city>Chicago</city><city>New York</city></xml>'

And this can be selected against as a table. This would have a slightly different behaviour than Macros's in that it will return rows for cities where the population is zero.

SELECT 
 tempTable.item.value('.', 'varchar(50)') AS City,
    COUNT(DISTINCT people) AS [Population]
FROM @Cities.nodes('//city') tempTable(item) 
LEFT OUTER JOIN peopleTable 
ON tempTable.item.value('.', 'varchar(50)') = peopleTable.City

The above is the first time I have attempted that approach though so I'd be glad to have it critiqued!

My habitual approach is to pass it in as a comma delimited list and use a split function to get it into table format that can be joined against. An example split function is here

Answer 4

If you are using SQL Server 2008, you can create a stored proc with a table variable as the input parameter. Then inside the proc you can just join to the input parameter table to get what you want.

Answer 5

You could use table valued parameters, but that's SQL Server 2008 and up only.

If the number of names is limited and you need to support older versions of SQL Server, you can use multiple parameters. That way you're still safe from SQL injection. I'm not entirely sure on what query you want to do, so I'll give an example based on your code:

Public Shared Function GetCAData(ByVal employeeName() As String) As DataTable
    Dim sql As StringBuilder = New StringBuilder()
    sql.Append("SELECT EMPLID, EMPLNME, DISP_TYPE, BEGIN_DTE FROM Corr WHERE EMPLNME IN (")

    Dim cmd As New SqlCommand()
    For I As Integer = 0 To employeeName.Length - 1
        sql.Append("@name").Append(I).Append(",")
        cmd.Parameters.AddWithValue("@name" & I, employeeName(I))
    Next

    sql.Remove(sql.Length - 1, 1).Append(")")
    cmd.CommandText = sql.ToString()

    Return GenericDataAccess.GetData(cmd)
End Function

(I'm sorry if my VB looks a little odd, I never use it anymore)

It actually builds a SQL statement dynamically, but the "dynamic" part is just a bunch of generated parameter names, which you then set. The maximum number of allowed parameters is 2000-something.