Can a User-Defined Table Type (UDTT) parameter be mixed with other types

Question

I have a stored procedure that takes 3 parameters, 1st and 2nd parameters are of type varchar, last one is user defined table type.

When I EXEC the Stored Procedure through ExecuteNonQuery, it throws an exception:

[System.Data.SqlClient.SqlException] = {"Operand type clash: nvarchar is incompatible with ttOrderItems"}

The ttOrderItems is the User-Defined Table Type.

Is this behaviour normal? It does not take mix of parameters when one of the parameter is User-Defined Table Type?

Here are the code fragments that call the stored procedure:

public DataSet execProc(string storedProcedureName, IDictionary<string, object> 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();
                }
            }
        }

---

private void SetupParams(string RoutineName, SqlCommand cmd, IDictionary<string, object> 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;
                }
            }
        }

When I exec the same proc through following T-SQL, it works as expected:

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

---

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] > getdate() then 'Availle only on or after '+Convert(varchar(30),[From],106)
                    when  datediff(DD,getdate(),isnull(nullif([To],''),'4712-01-01')) < 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()

)

---

ALTER proc [dbo].[prCatalogItems](@comno varchar(3),@cuno varchar(6),
                                  @items ttOrderItems readonly) as
Begin
    select * from dbo.ftCatalogItems(@comno,@cuno,@items)
end;

Answer 1

You don't need AddWithValue, nor do you need to specify the UDTT name as you are calling a stored procedure (specifying TypeName is only required for parameterized ad hoc SQL).

What is the type of the object in your prms collection? You have three options of what to pass in as the SqlParameter.Value ( http://msdn.microsoft.com/en-us/library/bb675163.aspx ):

1. DataTable
2. DbDataReader
3. method that returns IEnumerable

So, the main thing to do is:

1. Make sure you are passing in one of the above
2. Keep the line that does p.SqlDbType = SqlDbType.Structured;
3. Don't use `.ToString()';
4. Get rid of the entire cmd.Parameters.AddWithValue(p.ParameterName, p.Value.ToString()); line as the value was set via if (prms.Keys.Contains(pname)) p.Value = prms[pname];.
5. Move p.SqlDbType = SqlDbType.Structured; up to the switch (sx[0].ToLower()) and get rid of the if (sx[0] == "table type") block through the else, but obviously keep the cmd.Parameters.Add(p);.