0

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"

8
  • Does this answer your question? System.ArgumentException: The table type parameter must have a valid type name
    – Marco
    Commented Apr 20, 2022 at 9:42
  • Have a look at this and this
    – McNets
    Commented Apr 20, 2022 at 9:43
  • Try adding a table name to the constructor : DataTable InputRefTable = new DataTable("Table Name")
    – jdweng
    Commented Apr 20, 2022 at 10:21
  • @jdweng what should be the table name? Which table exactly is this about
    – WarMachine
    Commented Apr 20, 2022 at 10:46
  • The table name should be the type of @RefData. This should be a table-valued parameter of a particular table type. This is part of the declaration of the stored procedure. Commented Apr 20, 2022 at 10:52

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.