title: Passing multiselect selected items into SQL Server 2008
body: <p>I can't seem to find a good example of this online.  What I want to do is pass all of the values of a multiselect listbox into a SQL stored procedure to write them to a table.</p>
  
  <p>I know that if I want to pass one selected value to SQL I would do this:</p>
  
  <p><strong>C#:</strong></p>
  
  <pre><code>using (SqlCommand cmd = new SqlCommand("sp_RegisterUser", conn))
  {
      cmd.CommandType = CommandType.StoredProcedure;
      using (SqlDataAdapter da = new SqlDataAdapter(cmd))
          cmd.Parameters.Add("@UserPreferences", SqlDbType.Char).Value = MatchPrefs.SelectedValue;
          cmd.Parameters.Add("@UserName", SqlDbType.Char).Value = UserName.Text;
  
      conn.Open();
      cmd.ExecuteNonQuery();
  </code></pre>
  
  <p><strong>SQL:</strong> </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_RegisterUser]
      @UserPreferences varchar(max),
      @UserName varchar(25)
  
  AS
  BEGIN
  
  INSERT INTO tmpUserMatch (
      UserID,
      MatchField,
      MatchValue
  
      )
  VALUES (
      (SELECT Top 1 UserID FROM tmpUsers WHERE UserName = @UserName),
      'MatchPreferences',
      @UserPreferences
      )
  
  END     
  </code></pre>
  
  <p>But how would I do this if MatchPrefs is a multiselect?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have the C# part down, by replacing:</p>
  
  <pre><code>cmd.Parameters.Add("@UserPreferences", SqlDbType.Char).Value = MatchPrefs.SelectedValue;
  </code></pre>
  
  <p>with:</p>
  
  <pre><code>var selectedInterests = MatchPrefs.Items.Cast&lt;ListItem&gt;().Where(item =&gt; item.Selected).Select(item =&gt; item.Value).ToList();
  string strInterests = String.Join(",", selectedInterests).TrimEnd();
  cmd.Parameters.Add("@UserPreferences", SqlDbType.Char).Value = strInterests;
  </code></pre>
  
  <p>and on the SQL end I have a function that will create a table-valued parameter:</p>
  
  <pre><code>SELECT * FROM dbo.CSVToTable(@UserPreferences)
  </code></pre>
  
  <p>Just need some help completing the SQL side with the 2 additional fields I need to include; UserID and MatchField.</p>