Categories
parameters sql sql-server-2008

Parameterize an SQL IN clause

1113

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

In this query, the number of arguments could be anywhere from 1 to 5.

I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to SQL Server 2008, I am open to that.

3

325

Here’s a quick-and-dirty technique I have used:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

So here’s the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Two caveats:

  • The performance is terrible. LIKE "%...%" queries are not indexed.
  • Make sure you don’t have any |, blank, or null tags or this won’t work

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

18

  • 2

    yeah, it is 10x slower, but it’s very easily parameterized, heh. Not sure how much faster it would be to call fnSplit() as proposed by Longhorn213’s answer

    Dec 3, 2008 at 16:48

  • 14

    Yes, this is a table scan. Great for 10 rows, lousy for 100,000.

    Dec 3, 2008 at 16:48

  • 17

    Make sure you test on tags that have pipes in them.

    Dec 3, 2008 at 17:16

  • 18

    This doesn’t even answer the question. Granted, it’s easy to see where to add the parameters, but how can you accept this a solution if it doesn’t even bother to parameterize the query? It only looks simpler than @Mark Brackett’s because it isn’t parameterized.

    Dec 3, 2008 at 20:14

  • 23

    What if your tag is ‘ruby|rails’. It will match, which will be wrong. When you roll out such solutions, you need to either make sure tags do not contain pipes, or explicitly filter them out: select * from Tags where ‘|ruby|rails|scruffy|rubyonrails|’ like ‘%|’ + Name + ‘|%’ AND name not like ‘%!%’

    – A-K

    Aug 19, 2009 at 22:21

325

Here’s a quick-and-dirty technique I have used:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

So here’s the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Two caveats:

  • The performance is terrible. LIKE "%...%" queries are not indexed.
  • Make sure you don’t have any |, blank, or null tags or this won’t work

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

18

  • 2

    yeah, it is 10x slower, but it’s very easily parameterized, heh. Not sure how much faster it would be to call fnSplit() as proposed by Longhorn213’s answer

    Dec 3, 2008 at 16:48

  • 14

    Yes, this is a table scan. Great for 10 rows, lousy for 100,000.

    Dec 3, 2008 at 16:48

  • 17

    Make sure you test on tags that have pipes in them.

    Dec 3, 2008 at 17:16

  • 18

    This doesn’t even answer the question. Granted, it’s easy to see where to add the parameters, but how can you accept this a solution if it doesn’t even bother to parameterize the query? It only looks simpler than @Mark Brackett’s because it isn’t parameterized.

    Dec 3, 2008 at 20:14

  • 23

    What if your tag is ‘ruby|rails’. It will match, which will be wrong. When you roll out such solutions, you need to either make sure tags do not contain pipes, or explicitly filter them out: select * from Tags where ‘|ruby|rails|scruffy|rubyonrails|’ like ‘%|’ + Name + ‘|%’ AND name not like ‘%!%’

    – A-K

    Aug 19, 2009 at 22:21

260

For SQL Server 2008, you can use a table valued parameter. It’s a bit of work, but it is arguably cleaner than my other method.

First, you have to create a type

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

Then, your ADO.NET code looks like this:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

Update
As Per @Doug

Please try to avoid var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);

It’s set first value length, so if first value is 3 characters then its set max length 3 and other records will truncated if more then 3 characters.

So, please try to use: var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, maxLen);

Note: -1 for max length.

14

  • 43

    we tested this and table valued parameters are DOG slow. It is literally faster to execute 5 queries than it is to do one TVP.

    Apr 4, 2011 at 5:19

  • 4

    @JeffAtwood – Have you tried reshuffling the query to something like SELECT * FROM tags WHERE tags.name IN (SELECT name from @tvp);? In theory, this really should be the fastest approach. You can use relevant indexes (e.g. an index on tag name that INCLUDEs count would be ideal), and SQL Server should be doing a few seeks to grab all the tags and their counts. What does the plan look like?

    Oct 13, 2011 at 19:47

  • 9

    I’ve also tested this and it is FAST AS LIGHTNING (compared to constructing a large IN string). I had some problems setting the parameter though since I was constantly getting “Failed to convert parameter value from a Int32[] to a IEnumerable`1.”. Anyway, solved that and here’s a sample I made pastebin.com/qHP05CXc

    May 2, 2013 at 13:49

  • 6

    @FredrikJohansson – Out of 130 upvotes, you may be the only run that’s actually tried to run this! I made a mistake reading the docs, and you actually need an IEnumerable<SqlDataRecord>, not just any IEnumerable. Code has been updated.

    May 2, 2013 at 18:17


  • 3

    @MarkBrackett Great with an update! Accually this code really saved the day for me since I’m quering a Lucene search-index and it sometimes returns more than 50.000 or so hits that need to be doublechecked against SQL server – So I create an array of int[] (document/SQL keys) and then the code above comes in. The whole OP now takes less than 200ms 🙂

    May 3, 2013 at 6:57