0

I have a stored procedure InsertData with two TVP MessagesTVP & ExceptionTVP.

When I called it using EF, at any given point of time, I want to pass just one parameter.

It is working fine when I am passing

SqlParameter _dataTypeParam = new SqlParameter("MessageTVP", SqlDbType.Structured);

DataTable _InsertTMessageTVPDatatable = new DataTable("MessageTVP");
_dataTypeParam.Value = _InsertTMessageTVPDatatable;
_dataTypeParam.TypeName = "dbo.MessageTVP";

var obj = dbContext.Database.ExecuteSqlCommand($"InsertData @MessageTVP", _dataTypeParam);

But same is failing with error

Operand type clash: ExceptionTVP is incompatible with MessageTVP

when I am passing just ExceptionTVP:

SqlParameter _dataTypeParam = new SqlParameter("ExceptionTVP", SqlDbType.Structured);

DataTable _InsertTExceptionTVPDatatable = new DataTable("ExceptionTVP");
_dataTypeParam.Value = _InsertTExceptionTVPDatatable;
_dataTypeParam.TypeName = "dbo.ExceptionTVP";

var obj = dbContext.Database.ExecuteSqlCommand($"InsertData @ExceptionTVP", _dataTypeParam);

This is how stored procedure is defined in SQL Server:

CREATE PROCEDURE [dbo].[InsertTelemetryData]
    @MessageTVP AS MessageTVP READONLY,
    @ExceptionTVP AS ExceptionTVP READONLY

Can anyone help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anil Purswani
  • 1,857
  • 6
  • 35
  • 63
  • You are not using EntityFramework here. Have you tried to pass null for first parameter and ExceptionTVP for second? – Adil Mammadov Jul 18 '16 at 05:50
  • @AdilMammadov, I am using EF dbContext comes from EF. Yeah I tried passing null values but it returned error - "DBNull value for parameter 'MessageTVP' is not supported. Table-valued parameters cannot be DBNull." – Anil Purswani Jul 18 '16 at 05:56
  • @AnilPurswani, well. The error message is correct. You can't pass `NULL` for table-valued parameter. You have to always pass both parameters. The table in the the table-valued parameter can be empty (0 rows), but you have to pass it. – Vladimir Baranov Jul 18 '16 at 06:03
  • @VladimirBaranov, Could you please let me know what should I pass in first parameter to keep it empty. A sample code may help here. – Anil Purswani Jul 18 '16 at 06:06
  • 1
    @AnilPurswani, I can't give you a specific example. I don't use this style of C# code. You create `DataTable`. Then you add rows to it somewhere. Then you set `DataTable` as a parameter value for the stored procedure call. Do all this, but don't add rows to the `DataTable`. – Vladimir Baranov Jul 18 '16 at 06:11

0 Answers0