title: C# Linq to SQL pass List in stored procedure
body: <p>I'm looking to pass a List of string in my stored procedure, I tried to set up the indications of the following tutorial but I'm stuck in last step: <a href="https://larrymcnutt.wordpress.com/2014/11/05/passing-a-list-int-from-c-to-a-t-sql-stored-procedure/" rel="nofollow noreferrer">https://larrymcnutt.wordpress.com/2014/11/05/passing-a-list-int-from-c-to-a-t-sql-stored-procedure/</a></p>
  
  <p>I created a user-defined type like this in my database :</p>
  
  <pre><code>create type dbo.myList as table
  (
      element nvarchar(22)
  )
  go
  </code></pre>
  
  <p>In my class, I adapt my code with the right type and the List</p>
  
  <pre><code>new SqlMetaData("element", SqlDbType.NVarChar, 22)
  </code></pre>
  
  <p>But the problem I encounter is that my stored procedure return multiple values, so i use a method like this : </p>
  
  <pre><code>    [Function(Name = "dbo.MyStoredProcedure_S")]
      [ResultType(typeof(Value))]
      [ResultType(typeof(Structure))]
      [ResultType(typeof(Error))]
      public IMultipleResults MyStoredProcedure_SMultipleResults(
          [Parameter(Name = "id", DbType = "Int NOT NULL")] int id,
          [Parameter(Name = "myList", DbType = "Structured")] myCollection myList)
      {
          var result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id, myList);
          return (IMultipleResults)(result.ReturnValue);
      }
  </code></pre>
  
  <p>I have not found a resource indicating how to pass my list as a parameter in this case.</p>
  
  <p>The exception returned :</p>
  
  <blockquote>
    <p>The table type parameter '@p1' must have a valid type name.</p>
  </blockquote>
  
  <p>I also tried to change the type of my variable by a simple list of string like this </p>
  
  <pre><code>[Parameter(Name = "myList", DbType = "Structured")] List&lt;string&gt; myList
  </code></pre>
  
  <p>but I have the same result</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT:</strong> this is my code used for the stored procedure : </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[myStoredProcedure]
      @id int,
      @myList myList READONLY,
  AS
  BEGIN
      SELECT ...
      WHERE ref IN (SELECT element FROM @myList)
      ...
  END
  </code></pre>
  
  <p>And I manage to exec my stored procedure like this :</p>
  
  <pre><code>DECLARE @return_value int
  DECLARE @list [dbo].[myList]
  INSERT INTO @list VALUES ('stringval'), ('otherstringval');
  
  EXEC    @return_value = [dbo].[sp_ATL_RechercheHuilesMagasin_S]
      @id = 1,
      @myList = @list
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>It returns the data in my 3 tables corresponding to the results "Value", "Structure", "Error" defined in my c# code</p>