Regarding a question and its related answer, I'm trying to create a new table type using SQL queries and run a join query on data which are of this table type.
I put both queries in the same script, i.e. create type and select but it is not working. My query is as below (the whole code is brought in the end of question):
IF TYPE_ID(N'BranchMappingType') IS NULL
CREATE TYPE BranchMappingType As Table
(COL_ServerID int,
COL_ServerSchema Common._SMALL_,
COL_TableName Common._SMALL_,
COL_BranchNo Common._SMALL_,
COL_BranchSchema Common._SMALL_ );
SELECT [VMA_BranchID] FROM [VIP].[VipMapping]
JOIN @TVP ON
VMA_ServerID=COL_ServerID AND
VMA_ServerSchema=COL_ServerSchema AND
VMA_TableName=COL_TableName AND
VMA_BranchNo=COL_BranchNo AND
VMA_BranchSchema=COL_BranchSchema;
I will execute my query using SqlDataReader.ExecuteReader but It will throw exception:
Column, parameter, or variable @TVP. : Cannot find data type BranchMappingType.
But when I run 1st part of query, i.e., IF TYPE_ID(...) ... CREATE TYPE it will execute with no errors and then running the second part i.e. Select from ... join @TVP works flawlessly.
It seems newly created table type is not recognized when I run both queries with single execution of SqlDataReader.ExecuteReader.
I want to know what is the reason and If I've made some mistakes during my implementation or not? I prefer to run both queries with one-time calling of SqlDataReader.ExecuteReader method.
Here is full code for better inspection:
List<int> keys = new List<int>();
if (foreignKeyInfoList == null || foreignKeyInfoList.Count == 0)
return new StatusResponseKeysList { ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT };
const string ID_FIELD = "VMA_BranchID";
const string STRUCTURED_DATA_TABLE_NAME = "@TVP";
const string COL1_SERVER_ID = "COL_ServerID";
const string COL2_SERVER_SCHEMA = "COL_ServerSchema";
const string COL3_TABLE_NAME = "COL_TableName";
const string COL4_BRANCH_NO = "COL_BranchNo";
const string COL5_BRANCH_SCHEMA = "COL_BranchSchema";
//const string COL6_TABLE_ID = "COL_TableID";
const string MAP1_SERVER_ID = "VMA_ServerID";
const string MAP2_SERVER_SCHEMA = "VMA_ServerSchema";
const string MAP3_TABLE_NAME = "VMA_TableName";
const string MAP4_BRANCH_NO = "VMA_BranchNo";
const string MAP5_BRANCH_SCHEMA = "VMA_BranchSchema";
const string TYPE_NAME = "BranchMappingType";
//const string MAP6_TABLE_ID = "VMA_TableID";
string sqlQuery = $"IF TYPE_ID(N'{TYPE_NAME}') IS NULL" + " " +
$"CREATE TYPE {TYPE_NAME} As Table" + " " +
$"({COL1_SERVER_ID} int," + " " +
$"{COL2_SERVER_SCHEMA} Common._SMALL_," + " " +
$"{COL3_TABLE_NAME} Common._SMALL_," + " " +
$"{COL4_BRANCH_NO} Common._SMALL_," + " " +
$"{COL5_BRANCH_SCHEMA} Common._SMALL_" + " " +
$"); ";
sqlQuery += $"SELECT [{ID_FIELD}] " +
$"FROM {Settings.VIP_MAPPING_TABLE} " +
$"JOIN {STRUCTURED_DATA_TABLE_NAME} ON " +
$"{MAP1_SERVER_ID}={COL1_SERVER_ID} " +
$" AND {MAP2_SERVER_SCHEMA}={COL2_SERVER_SCHEMA} " +
$" AND {MAP3_TABLE_NAME}={COL3_TABLE_NAME} " +
$" AND {MAP4_BRANCH_NO}={COL4_BRANCH_NO} " +
$" AND {MAP5_BRANCH_SCHEMA}={COL5_BRANCH_SCHEMA} ";// +
//$" AND {MAP6_TABLE_ID}={COL6_TABLE_ID}";
DataTable dataTable = new DataTable(TYPE_NAME);
dataTable.Columns.Add(COL1_SERVER_ID, typeof(int));
dataTable.Columns.Add(COL2_SERVER_SCHEMA, typeof(string));
dataTable.Columns.Add(COL3_TABLE_NAME, typeof(string));
dataTable.Columns.Add(COL4_BRANCH_NO, typeof(string));
dataTable.Columns.Add(COL5_BRANCH_SCHEMA, typeof(string));
//dataTable.Columns.Add(COL6_TABLE_ID, typeof(int));
for (int i = 0; i < foreignKeyInfoList.Count; i++)
{
ForeignKeyLookupModelInBranchSide oneKeySet = foreignKeyInfoList[i];
DataRow row = dataTable.NewRow();
row[COL1_SERVER_ID] = Int32.Parse(oneKeySet.ServerID);
row[COL2_SERVER_SCHEMA] = oneKeySet.ServerSchema;
row[COL3_TABLE_NAME] = oneKeySet.TableName;
row[COL4_BRANCH_NO] = oneKeySet.BranchNo;
row[COL5_BRANCH_SCHEMA] = oneKeySet.BranchSchema;
dataTable.Rows.Add(row);
}
try
{
using (SqlConnection sqlConnection = new SqlConnection(Settings.connectionString))
{
SqlCommand command = new SqlCommand(sqlQuery, sqlConnection);
SqlParameter p = command.Parameters.Add(STRUCTURED_DATA_TABLE_NAME, SqlDbType.Structured);
p.Value = dataTable;
p.TypeName = TYPE_NAME;
sqlConnection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
keys.Add(reader.GetInt32(i));
}
}
}
else
{
}
sqlConnection.Close();
}
return new StatusResponseKeysList
{
keysList = keys,
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT
};
}
catch (Exception e)
{
//Exception caught here !!!!!!!:
//"Column, parameter, or variable @TVP. : Cannot find data type BranchMappingType."
if (e.GetType() == typeof(SqlException))
{
//this is a db error
return new StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_014_DATABASE_EXCEPTION
};
}
else
{
//other types of erros (not a DB-error)
return new StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_001_COMMON_ERROR
};
}
}
The table type must exist before the parameterized query that uses the type is executed. The reason is the database engine first creates an internal table in tempdb matching the table type defined on the server before the batch executes. It then bulk inserts the TVP value into tempdb using the structured parameter value and meta-data provided by the API. Only after the table is loaded does the batch execute, where it can use the TVP value.
The implication is that one cannot create the table type and use it in the same batch that uses the type as a parameter.