ProAnswers.org

How do I parameterize a SQL query containing an IN clause?

See this query containing number of arguments:

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 fancy SQL Server 2008 specific way of doing it.

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 
			cmd.Parameters.AddWithValue("@tagNames", tags).SqlDbType = SqlDbType.Structured; 
			cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";