无法访问存储过程中的表变量

问题描述

我以这种方式将数据表从代码传递到存储过程.

I am passing a datatable from Code to stored procedure in this way.

DataTable table = CommonFunctions.ToDataTable(request);
object[] spParams = new object[1];
spParams[0] = table;

DbCommand dbCommand =
  db.GetStoredProcCommand("OS_UpdateOrgStructureDetails", spParams);

我试图在存储过程中访问这个参数.

I am trying to access this parameter in stored proc.

CratePROCEDURE OS_UpdateOrgUnits
 @table OS_RenameNodeTable READONLY
AS
BEGIN
    UPDATE OrgUnit
    SET DetailName = ut.NewValue
    FROM @table ut
    INNER JOIN OrgUnit ou ON ou.OrgUnitID = ut.OrgUnitID
END

但是当调用存储过程时它会抛出一个错误.

But when the call is made to stored procedure it throws an error.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol
stream is incorrect. Table-valued parameter 1 ("@table"), row 0, column 0:
Data type 0xF3 (user-defined table type) has a non-zero length database name
specified.  Database name is not allowed with a table-valued parameter, only
schema name and type name are valid.

无法解决错误.

推荐答案

由于 SqlCommandBuilder.DeriveParameters 方法中的错误，表值参数的 SqlParameter 对象的 TypeName 属性包含数据库名称(请参阅http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx，注释表值参数输入不正确").

Because of a bug in the SqlCommandBuilder.DeriveParameters method, the TypeName property of the SqlParameter object for the table valued parameter contains the database name (see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx, the comment "Table valued parameters not typed correctly").

修复此问题，您可以在创建命令后立即添加此通用代码:

The fix this you can add this general purpose code right after creating the command:

foreach (SqlParameter parameter in dbCommand.Parameters)
{
    if (parameter.SqlDbType != SqlDbType.Structured)
    {
        continue;
    }
    string name = parameter.TypeName;
    int index = name.IndexOf(".");
    if (index == -1)
    {
        continue;
    }
    name = name.Substring(index + 1);
    if (name.Contains("."))
    {
        parameter.TypeName = name;
    }
}

这篇关于无法访问存储过程中的表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！