I am constructing a data table and passing it as a parameter to the stored procedure. After execution, the stored procedure will provide a result set.
@RefData is the Table parameter name.
CREATE TYPE [dbo].[tvpKeyValue] AS TABLE(
[KeyID] [bigint] NULL,
[KeyValue] [bigint] NULL,
[KeyValueText] [varchar](500) NULL
)
//Data table
DataTable InputRefTable = new DataTable();
//Adding columns
DataColumn col = null;
col = new DataColumn("KeyID", typeof(Int32));
InputRefTable.Columns.Add(col);
col = new DataColumn("KeyValue", typeof(Int64));
InputRefTable.Columns.Add(col);
col = new DataColumn("KeyValueText", typeof(String));
InputRefTable.Columns.Add(col);
//Adding row values
DataRow workRow;
foreach (var item in referenceListInfo)
{
string genName = string.Empty;
var refHeadingXid = context.ViewBookingReferenceDescription.FirstOrDefault(c => c.CompanyID == companyId && c.Description == item.name)?.ReferenceHeadingID;
if (refHeadingXid != null)
{
workRow = InputRefTable.NewRow();
workRow[0] = Convert.ToInt64(refHeadingXid);
workRow[1] = Convert.ToInt64(refHeadingXid);
workRow[2] = item.value;
InputRefTable.Rows.Add(workRow);
}
}
//Parameters
var param = new SqlParameter[]
{
new SqlParameter("@CompanyID", companyId),
new SqlParameter ("@RefData",InputRefTable)
};
param[1].SqlDbType = SqlDbType.Structured;
param[1].Direction = ParameterDirection.Input;
param[1].TypeName = "dbo.tvpKeyValue";
var rawSql = "EXECUTE usp_triprequest_GET_DynamicRefData @CompanyID, @RefData";
var a = context.ReferenceTableOutput.FromSql(rawSql, param).ToList();
Earlier I got the following exception when I did not provide Typename
The table type parameter '@RefData' must have a valid type name
Then added line param[1].TypeName = "dbo.tvpKeyValue"; Getting exception
"Operand type clash: tvpKeyValue is incompatible with bigint"
@RefData
. This should be a table-valued parameter of a particular table type. This is part of the declaration of the stored procedure.