"Error converting data type nvarchar to int" when executing Stored Procedure and reading return value

Question

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.

Code Calling the 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

The clr sp code:

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());
        }

    }
}

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

Please always post the full and exact error message, not just part of it. Thanks. — Solomon Rutzky
– Solomon Rutzky, Commented May 25, 2018 at 15:17

Solomon Rutzky · Accepted Answer · 2018-05-25 16:12:22Z

The main problem is with this line:

AddWithValue("@Result", SqlDbType.NVarChar )

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.

A secondary problem is:

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.

Several other things:

Your .NET code returns void from the method, so no use capturing @return_value
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.
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
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.
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.
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().

Collectives™ on Stack Overflow

"Error converting data type nvarchar to int" when executing Stored Procedure and reading return value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related