Operand type clash: varchar is incompatible

Question

I have created a user data table type as below:

CREATE TYPE [dbo].[UDTPASpecMYTest] AS TABLE(
    [EmpName] [varchar](max) NULL,
    [Empaddress] [varchar](max) NOT NULL,
    [EmpCarname] [varchar](max) NULL     
)
GO

and declare a procedure as below:

CREATE procedure [dbo].[test]
(
      @tblPASpecs UDTPASpecMYTest  READONLY    
)
AS
BEGIN
select 1
END

While I am calling the procedure from application by passing a datatable it is showing an error:

"Operand type clash: nvarchar is incompatible with UDTPASpecMYTest".

Code in application:

DataColumn workCol = dtbl.Columns.Add("EmpName", typeof(String));   
dtbl.Columns.Add("Empaddress", typeof(String));
dtbl.Columns.Add("EmpCarname", typeof(String));
dtbl.Rows.Add("Test", "Test", "Test");
strQuery = "EXEC dbo.test @tblPASpecs=" + dtbl + "";

//call the procedure
CMASConnectionProvider.DMLService.ExecSqlReturnDataSet(strQuery);

You have to pass it as a parameter, you cannot use string concatenation here. — juharr, Jul 29 '15 at 12:27
This should help you: https://msdn.microsoft.com/en-us/library/bb675163.aspx — jpw, Jul 29 '15 at 12:28
To use SQL user types in C# you must inform its type. If you're using ADO.Net and are executing a stored procedure, when you define the parameter, you must specify type as SqlDbType.Structured and the type name with name of your Type, = "dbo.UDTPASpecMYTest"; — Ricardo Pontual, Jul 29 '15 at 12:29
This [question](http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net) shows how to do it with ADO.Net. — juharr, Jul 29 '15 at 12:30

g.pickardou · Answer 1 · 2015-07-29T12:44:19.830

You are only passing the type name (as string) to your stored procedure. Instead you must pass the table instance, by using SqlParameter object.

Something like this:

var connection = CMASConnectionProvider.Connection;
var command = new SqlCommand("dbo.test", connection);
command.CommandType = CommandType.StoredProcedure;

// Next 2 lines are the point:
var parameter = command.Parameters.AddWithValue("@tblPASpecs", dtbl); 
parameter.SqlDbType = SqlDbType.Structured; 

// Execute the command according your needs and existing helper classes
// var result = command.Execute();

This article explains exactly what you would like to do, please read here

You really should include the relevant parts of the link in your answer to guard against the the possibility of the link going dead. — juharr, Jul 29 '15 at 12:34

Operand type clash: varchar is incompatible

1 Answers1