How do I provide a string of Ids in a Select statement's IN clause even though the column is an integer

Question

I'm paging data using an ObjectDataSource and I have the following method:

public int GetNumberOfArticles(string employeeIds)
{
    System.Data.DataTable dataTable;
    System.Data.SqlClient.SqlDataAdapter dataAdapter;
    System.Data.SqlClient.SqlCommand command;

    int numberOfArticles = 0;

    command = new System.Data.SqlClient.SqlCommand();
    command.Connection = Classes.Database.SQLServer.SqlConnection;

    command.CommandText = @"SELECT COUNT(*)
                            FROM 
                                  [Articles]
                            WHERE 
                                  [Articles].[EmployeeID] IN (@EmployeeIds)";

    command.Parameters.AddWithValue("@EmployeeIds", employeeIds);
    numberOfArticles = (int)command.ExecuteScalar();
    return numberOfArticles;
}

EmployeeID is an integer and because of that, anything I place inside employeeIds will be converted to an integer. However, because I'm using the IN keyword, it is obvious that i want to replace employeeIds with a list of ids separated by commas:

1, 2, 3

But when I replace the line:

command.Parameters.AddWithValue("@EmployeeIds", employeeIds);

with something like:

command.Parameters.AddWithValue("@EmployeeIds", "1, 2, 3");

I receive an exception because I provided a string while EmployeeIds is an integer. So, how would i go about doing that?

thanks.

Edit:

From the replies, I understand that this has to be done manually, however the class which contains this method would be created automatically by the ObjectDataSource. So how can i provide the values of employeeIds at runtime?

score 2 · Answer 1 · answered Apr 12 '09 at 13:47

The only way to go about doing this would be to parse your string manually in your query and insert the values into a memory table, then join the memory table in your query rather than using the IN clause.

As an example, this page on CodeProject presents this function:

IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID(’UF_CSVToTable’))
 DROP FUNCTION UF_CSVToTable
GO

CREATE FUNCTION UF_CSVToTable
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END
Go

You could then use it like this:

SELECT                          
    COUNT(*)
FROM 
    [Articles]

JOIN dbo.UF_CSVToTable(@EmployeeIds) ids on ids.sID = [Articles].[EmployeeID]

In the end, though, it's generally not a great practice to take up. But if it's necessary, then this approach should give it to you in a pretty straightforward manner.

+1, but why do you say it's not great practice? If your DB supports UDFs, it's a good solution. For more complex queries, this solution gives you the benefit of prepared execution: http://technet.microsoft.com/en-us/library/ms131667.aspx — Joe, Apr 12 '09 at 17:22
It's bad practice because it doesn't (and can't) scale well. If you take this approach, you're limited by the maximum capacity of the data type. If you go the dynamic SQL route you lose the advantage of a reuseable execution plan and assume that dynamic SQL is allowed. — Adam Robinson, Apr 12 '09 at 17:35

score 2 · Answer 2 · answered Apr 12 '09 at 14:21

2

You can create your SQL-statement like this using string-concatenation:

"SELECT ... IN (@id1,@id2,@id3)"

and then:

command.Parameters.AddWithValue("@id1", employeeIds[0]); command.Parameters.AddWithValue("@id2", employeeIds[1]); command.Parameters.AddWithValue("@id3", employeeIds[2]);

(using for-loops of course).

This way you still insert values using parameters.

answered Apr 12 '09 at 14:21

Erik

4,120
2
27
20

thnx, but i don't know how many Ids there will be. Only at runtime will i know. – bahith Apr 12 '09 at 14:39
It's ok that you don't know the number of IDs, create the SQL dynamically. – Daniel Apr 12 '09 at 14:45

score 1 · Answer 3 · edited May 23 '17 at 12:15

Similar to this question, you need to add a parameter for each value at runtime:

public int GetNumberOfArticles(string employeeIds)
{
    System.Data.DataTable dataTable;
    System.Data.SqlClient.SqlDataAdapter dataAdapter;
    System.Data.SqlClient.SqlCommand command;

    int numberOfArticles = 0;

    command = new System.Data.SqlClient.SqlCommand();
    command.Connection = Classes.Database.SQLServer.SqlConnection;

    string params = string.Join(",", employeeIds.Select((e, i)=> "@employeeId" + i.ToString()).ToArray());
    command.CommandText = @"SELECT                          
                               COUNT(*)
                            FROM 
                               [Articles]
                            WHERE 
                               [Articles].[EmployeeID] IN (" + params + ")";

    for (int i = 0; i < employeeIds.Length; i++) {
       command.Parameters.AddWithValue("@employeeId" + i.ToString(), employeeIds[i]);
    }

    numberOfArticles = (int)command.ExecuteScalar();

    return numberOfArticles;
}

score 0 · Answer 4 · answered Apr 12 '09 at 14:00

A parameter is a distinct value and it can't be used to dynamically create a SQL statement as you're trying to do. You will need to create the SQL statement using string concatenation which could be as simple as:

command.CommandText = @"SELECT COUNT(*) FROM [Articles] " +
    "WHERE [Articles].[EmployeeID] IN (" + employeeIds + ")";

However, you need to sanitize the employeeIds string if it comes from user input to avoid SQL injection vulnerability. You could do that by splitting the string into an array and parsing each element to check that it's an int. If it passes, then you can re-join the array into a string before concatenating.

How do I provide a string of Ids in a Select statement's IN clause even though the column is an integer

4 Answers4