title: Can a User-Defined Table Type (UDTT) parameter be mixed with other types
body: <p>I have a stored procedure that takes 3 parameters, 1st and 2nd parameters are of type varchar, last one is user defined table type. </p>
  
  <p>When I EXEC the Stored Procedure through <code>ExecuteNonQuery</code>, it throws an exception:</p>
  
  <blockquote>
    <p>[System.Data.SqlClient.SqlException] = {"Operand type clash: nvarchar is incompatible with ttOrderItems"}</p>
  </blockquote>
  
  <p>The <code>ttOrderItems</code> is the User-Defined Table Type.</p>
  
  <p>Is this behaviour normal? It does not take mix of parameters when one of the parameter is User-Defined Table Type?</p>
  
  <p>Here are the code fragments that call the stored procedure:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public DataSet execProc(string storedProcedureName, IDictionary&lt;string, object&gt; prms = null)
          {
              using (SqlCommand cmd = new SqlCommand(storedProcedureName, scon))
              {
                  DataSet rs = new DataSet();
                  if (prms != null) SetupParams(storedProcedureName, cmd, prms);
                  try
                  {
                      cmd.CommandText = storedProcedureName;
                      cmd.CommandType = CommandType.StoredProcedure;
                      cmd.Connection.Open();
                      //using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(rs);
                      //{
                      //    da.Fill(rs);
                      //}
                      cmd.ExecuteNonQuery();
                      cmd.Connection.Close();
                          return rs;
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      scon.Close();
                  }
              }
          }
  </code></pre>
  
  <hr>
  
  <pre class="lang-cs prettyprint-override"><code>private void SetupParams(string RoutineName, SqlCommand cmd, IDictionary&lt;string, object&gt; prms, bool keepConnectionOpen = true)
          {
              if (cmd != null) cmd.Parameters.Clear();
              string pname = "";
              DataTable tblParams = Select("Select * from dbo.ftRoutineSchema('" + RoutineName + "')");
              foreach (DataRow dr in tblParams.Rows)
              {
                  System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();
                  pname = dr["COLUMnNAME"].ToString().ToLower();
                  p.ParameterName = pname;
                  pname = pname.Remove(0, 1).ToLower(); // remove @  sign
                  if (prms.Keys.Contains(pname)) p.Value = prms[pname];
                  string direction = dr["Direction"].ToString().ToLower();
                  string sptype = (string)dr["DataType"];
                  string[] sx = dr["DataType"].ToString().Split(new char[] { '(', ',', ')' });
                  try
                  {
                      #region case type switch
                      switch (sx[0].ToLower())
                      {
                          case "int":
                              p.DbType = DbType.Int32;//=int.Parse(sx[2]);
                              break;
                          case "bigint":
                              p.DbType = DbType.Int64;
                              break;
                          case "varchar":
                              p.DbType = DbType.String;
                              p.Size = int.Parse(sx[1]);
                              break;
                          case "nvarchar":
                              p.DbType = DbType.String;
                              p.Size = int.Parse(sx[1]);
                              break;
                          case "decimal":
                              p.DbType = DbType.Decimal;
                              break;
                          case "datetime":
                              p.DbType = DbType.DateTime;
                              break;
                          case "ntext":
                          case "text":
                              p.DbType = DbType.String;
                              p.Size = 65536;
                              break;
  
                          default:
                              break;
                      }
                      switch (direction)
                      {
                          case "in": p.Direction = ParameterDirection.Input; break;
                          case "out": p.Direction = ParameterDirection.Output; break;
                          case "rc": p.Direction = ParameterDirection.ReturnValue; break;
                          default: break;
                      }
                      #endregion
                      if (sx[0] == "table type")
                      {
                          p.SqlDbType = SqlDbType.Structured;
  
                          cmd.Parameters.AddWithValue(p.ParameterName, p.Value.ToString());
                      }
                      else
                          cmd.Parameters.Add(p);
                  }
  
                  catch (Exception ex)
                  {
                      throw ex;
                  }
              }
          }
  </code></pre>
  
  <p>When I exec the same proc through following T-SQL, it works as expected:</p>
  
  <pre class="lang-sql prettyprint-override"><code>use edi
  go
  declare @items dbo.ttOrderItems
  insert @items
              select 1,'574114-023',1,'EA',720,'2014-Oct-14',null,null
  union all select 2,'574116-035',8,'EA',1865.5,'2014-Oct-10',null,null
  exec dbo.prCatalogItems '010','000164',@items
  </code></pre>
  
  <hr>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE function [dbo].[ftCatalogItems](@comno varchar(3),@cuno varchar(6),@items  ttOrderItems readonly) returns table as
      /*-------------------------------------------------------
      DECLARE @COMNO VARCHAR(3)='010',@CUNO VARCHAR(6)='000164'
      declare @items ttOrderItems;
      insert @items( position,ItemCode        ,QtyOrdered ,UOM    ,PriceQuoted,RequiredBy     ,ExpectedOnDock ,BackOrdered)
      select                              1,'1231-221'    ,1                  ,'EA'   ,20.20          ,'2014-11-01'   ,'2014-11-01'       ,0
      union  select                   2,'110223-245',10                   ,'EA'   ,2001.20        ,'2014-11-01'   ,'2014-11-01'       ,0
  
  
      select * from @items
      --------------------------------------------------------*/
      return(
  
      select 
                  Position
                  ,ItemCode
                  ,QtyOrdered
                  ,'EA' UOM
              ,PriceQuoted
              ,RequiredBy
              ,Isnull(c.Net,0.00) Net
              ,[Qty.] QtyApplicable
                  ,Status=case 
                      when ItemCode is null then 'Not in Catalog' 
                      when [From] &gt; getdate() then 'Availle only on or after '+Convert(varchar(30),[From],106)
                      when  datediff(DD,getdate(),isnull(nullif([To],''),'4712-01-01')) &lt; 1 then 'EXPIRED'
                      when items.PriceQuoted != c.Net then 'Quoted Price does not match Catalog price'
                      else coalesce(c.[Item Code],'Invalid/non-existent Item')
                  end 
  
  from @items items
  Left Join ediCatalog c on ltrim(c.[Item Code])=[ItemCode] AND COMNO=@COMNO AND c.[Customer Id.] =@CUNO  and c.[Server]=dbo.fsBaanServer()
  
  )
  </code></pre>
  
  <hr>
  
  <pre class="lang-sql prettyprint-override"><code>ALTER proc [dbo].[prCatalogItems](@comno varchar(3),@cuno varchar(6),
                                    @items ttOrderItems readonly) as
  Begin
      select * from dbo.ftCatalogItems(@comno,@cuno,@items)
  end;
  </code></pre>