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;
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 ):
So, the main thing to do is:
p.SqlDbType = SqlDbType.Structured;cmd.Parameters.AddWithValue(p.ParameterName, p.Value.ToString()); line as the value was set via if (prms.Keys.Contains(pname)) p.Value = prms[pname];.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);.