title: Alternatives to large IN clause in JDBC, how to use Table Value Parameters from Java?
body: <p>I'm using SQL Server 2008 RC2.</p>
  
  <p>We often have to do a query similar to this:</p>
  
  <pre><code>select * from Site s where s.name in (:names)
  </code></pre>
  
  <p>Where <code>":names"</code> is a big <code>StringBuilder</code> of 1000 comma-separated 18-byte names. This takes a while to run; up to a minute on my local development machine. I know that many people have suggested the use of <code>Table Valued Parameters</code> (TVP) for this, such as the example below:</p>
  
  <pre><code>create procedure [dbo].[sp_TestSites] @siteNames SiteNameTableType readonly
  as
  begin
      select s.* from Site s
         inner join @siteNames d on d.name = s.name
  end
  </code></pre>
  
  <p>But since the Microsoft JDBC Driver still doesn't support TVP (as far as I know), what is the best way to achieve this? Send one long String of comma separated values to a Stored Procedure and then split the string in the procedure and insert into a temp table? Example:</p>
  
  <pre><code>create type dbo.SiteNameTableType as table(
      name varchar(18) not null unique
  )
  
  create procedure sp_TestSites(
      @longStringOfNames varchar(max))
  as
  begin
      declare @siteNames SiteNameTableType
  
      insert into @siteNames
      select * from dbo.split(@longStringOfNames) -- assume I have a Split function
  
      select s.* from Site s
         inner join @siteNames d on d.name = s.name
  end
  </code></pre>