title: Pass list of values in sqlparameter, structured perhaps
body: <p>Is it possible to create something like this in C# code using SqlDbCommand and SqlParameter?</p>
  
  <pre><code>DECLARE @Users TABLE (ID INT)
  INSERT INTO @Users
  VALUES (10),(20),(30)
  
  SELECT COUNT(*) FROM @Users
  </code></pre>
  
  <p>I tried something like this:<br>
  1) DataTable creator:  </p>
  
  <pre><code>        private static DataTable CreateDataTable(IEnumerable&lt;int&gt; ids)
          {
          DataTable table = new DataTable();
          table.Columns.Add("ID", typeof(int));
          foreach (int id in ids)
          {
              table.Rows.Add(id);
          }
          return table;
  </code></pre>
  
  <p>2) Add SqlParameter:  </p>
  
  <pre><code> sqlParameters.Add(new SqlParameter()
                              {
                                  ParameterName = $"@paramname",
                                  SqlDbType = SqlDbType.Structured,
                                  Value = table
                              });
  </code></pre>
  
  <p>3) Execute command (command is SELECT COUNT(*) FROM @Users), parameters is list of parameters from step 2:  </p>
  
  <pre><code>using (SqlCommand cmd = new SqlCommand(command, connection))
                  {
                      if (parameters != null)
                          cmd.Parameters.AddRange(parameters.ToArray());
  
                      SqlDataReader reader = cmd.ExecuteReader();
  </code></pre>
  
  <p>What I get is:</p>
  
  <blockquote>
    <p>The table type parameter '@Users' must have a valid type name.</p>
  </blockquote>
  
  <p>And I don't really have a real table so no type available, just want it to be:</p>
  
  <blockquote>
    <p>DECLARE @Users TABLE (ID INT)</p>
  </blockquote>
  
  <p>Is that doable? What I want to achieve is just pass list of values, in this case list of ints, obviously.</p>
  
  <p><strong>IN REGARD TO MARKED AS DUPLICATE:</strong><br>
  Provided link doesn't solve the problem since it's not lack of typename problem but rather lack of typename to use. The problem is that I can't create any table and can't use any existing one to pass TypeName in SqlParameter.</p>
  
  <p><strong>ONE WORKING SOLUTION:</strong>  </p>
  
  <pre><code>CREATE TYPE [dbo].[IntList] AS TABLE(
  [Value] [int] NOT NULL
  )
  </code></pre>
  
  <p>and then SqlParameter:</p>
  
  <pre><code>sqlParameters.Add(new SqlParameter()
                          {
                              ParameterName = $"@paramname",
                              SqlDbType = SqlDbType.Structured,
                              Value = table,
                              TypeName = "dbo.IntList"
                          });
  </code></pre>
  
  <p>Nevertheless, another step would be to use built-in type like GarethD suggested. I'm not sure if they are available in SQL Server 2016.</p>