“将数据类型 nvarchar 转换为 int 时出错"；执行存储过程并读取返回值时 [英] "Error converting data type nvarchar to int" when executing Stored Procedure and reading return value

问题描述

我有一个 clr 存储过程，它有一个输出值，我使用下面的代码从 vb.net 应用程序调用 sp.我收到一条错误消息，显示将数据类型 nvarchar 转换为 int 时出错".1125092|63688|好的，这些值正是我期望从 sp 返回的值，但我不明白为什么我会收到转换错误，任何人都可以帮忙解决这个问题.

I have a clr stored procedure which has an output value I am calling the sp from a vb.net app with the code below. I am getting an error that says 'Error converting data type nvarchar to int. 1125092|63688|Ok' the values are exactly what I am expecting back from the sp but I can't understand why I am getting a convertion error can anyone help with this.

调用 sp 的代码:

Try
            Dim CN As New SqlConnection
            CN.ConnectionString = My.Settings.myConnection
            CN.Open()

            Dim Profile As String = "40T"
            Dim StartPosition As String = "53.6924582,-2.8730636000"
            Dim Destination As String = "46.18186,1.380222"
            Dim RetValue As String = "Testvalue"

            Dim test As String ="53.816408201,-2.990582799997"

            Dim cmd As SqlCommand = New SqlCommand("GetDistanceAndTime", CN)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "GetDistanceAndTime"
            cmd.Parameters.AddWithValue("@Profile", Profile )
            cmd.Parameters.AddWithValue("@StartPosition", StartPosition)
            cmd.Parameters.AddWithValue("@Destination", Destination)
            cmd.Parameters.AddWithValue("@Result", SqlDbType.NVarChar )
            cmd.Parameters("@Result").Direction = ParameterDirection.Output
            Try

             cmd.ExecuteNonQuery()
                RetValue = cmd.Parameters ("@Result").value
                If RetValue .Length > 2 Then

                End If
            Catch ex As Exception
                MessageBox.Show(Err.Description)
            End Try
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try

clr sp 代码:

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SqlStoredProcedure1(SqlString Profile, SqlString StartPosition, SqlString Destination, ref SqlString Result)
    {
        SqlString result;
        try
        {           
            SqlPipe sqlPipe = SqlContext.Pipe;

            result = DistanceRequest.InvokeService(Profile.ToString(), StartPosition.ToString(), Destination.ToString());
            Result = result.ToString();          
            SqlContext.Pipe.Send(result.ToString());
        }
        catch (Exception ex)
        {
            result = ex.Message;
            SqlContext.Pipe.Send(result.ToString());
        }

    }
}

奇怪的是，当我从 sql server manager 执行存储过程时，它会将返回值编译为 int！我更困惑了.

Oddly when I execute the stored procedure from sql server manager it compiles the return value as an int! I am even more baffled.

USE [myDB] GO

DECLARE @return_value int, @Result nvarchar(50)

EXEC @return_value = [dbo].[GetDistanceAndTime]
  @Profile = N'40T',
  @StartPosition = N'53.6924582,-2.8730636000',
  @Destination = N'46.18186,1.380222',
  @Result = @Result OUTPUT

SELECT  @Result as N'@Result'

SELECT  'Return Value' = @return_value

GO

推荐答案

主要问题在于这一行:

AddWithValue("@Result", SqlDbType.NVarChar )

问题是 SqlDbType.NVarChar 是一个 enum 却在此处被错误地用作暗示数据类型的值".默认情况下，数据类型是 int.这就是为什么您将存储过程的 Result 参数转换为通过上面显示的行为 SqlCommand 参数声明的 int 时出错的原因.

The problem is that SqlDbType.NVarChar is an enum yet is being used incorrectly here as the "value" to imply the datatype from. The datatype is, by default, int. This is why you are getting an error converting the stored procedure's Result parameter into the int that was declared for the SqlCommand parameter via the line shown above.

次要问题是:

虽然我不反对从 SQLCLR 中调用 Web 服务，但只有当进程完全包含在 T-SQL 中时才应该这样做.如果您已经使用 .NET 代码，那么执行存储过程只是为了调用 Web 服务是没有意义的.这对于应用层和数据库层来说都是非常低效的.

While I have nothing against calling Web Services from within SQLCLR, it should only be done when the process is wholly contained within T-SQL. If you are in .NET code already, it makes no sense to execute a stored procedure only to call a Web Service. That is highly inefficient, for both the app layer and the DB layer.

其他几件事:

1. 您的 .NET 代码从方法返回 void，因此没有使用捕获 @return_value
2. 不要使用 AddWithValue 方法.相反，使用正确的数据类型创建每个参数，然后设置值，然后将其添加到 Parameters 集合中.
3. 尽管这已经作为问题提到了，但对于将来创建这样的参数，即使使用 Add:AddWithValue("@Result", SqlDbType.NVarChar)将数据类型设置为字符串类型时，请务必同时设置maxsize/length
4. 这可能是一个问题:RetValue = cmd.Parameters ("@Result").valueSqlParameter 值属于 object 类型.因此，要么转换为字符串，要么将 .ToString() 添加到 value.
5. 您不需要对 SqlString 输入参数调用 ToString().所有 Sql* 类型都有一个 Value 属性，该属性以预期的 .NET 类型返回值.意思是，Profile.Value 返回您要查找的字符串.
6. 您创建了 SqlPipe sqlPipe = SqlContext.Pipe; 但从不使用它.您可以在 SqlContext.Pipe.Send() 的两个地方使用它.所以，改为使用:sqlPipe.Send().

1. Your .NET code returns void from the method, so no use capturing @return_value
2. Don't use the AddWithValue method. Instead, create each parameter with the correct datatype and then set the value, then add it to the Parameters collection.
3. Even though this was already mentioned as the problem, regarding creating parameters like this in the future, even using Add: AddWithValue("@Result", SqlDbType.NVarChar ) When setting the datatype to a string type, be sure to also set the maxsize / length
4. This could be a problem: RetValue = cmd.Parameters ("@Result").value SqlParameter values are of type object. So either cast to string or add .ToString() to value.
5. You don't need to call ToString() on the SqlString input parameters. All Sql* types have a Value property that returns the value in the expected .NET type. Meaning, Profile.Value returns the string you are looking for.
6. You create SqlPipe sqlPipe = SqlContext.Pipe; but never use it. You could use it in both places that you are doing SqlContext.Pipe.Send(). So, instead use: sqlPipe.Send().

这篇关于“将数据类型 nvarchar 转换为 int 时出错"；执行存储过程并读取返回值时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！