System.InvalidCastException in calling CLR stored procedure

Question

I am trying to debug a CLR stored procedure in Visual Studio 2012. When I call it, this error occurs and the CLR stored procedure is not getting called:

A .NET Framework error occurred during execution of user-defined routine or aggregate "CalculateResultByUser": 
System.InvalidCastException: No se puede convertir un objeto de tipo 'System.Data.SqlTypes.SqlInt32' al tipo 'System.IConvertible'.
System.InvalidCastException: 
   en System.Convert.ToInt32(Object value)
   en StoredProcedures.CalculateResultByUser(SqlInt32 idUsuario, SqlDateTime fechaInicio, SqlDateTime fechaFin)

This is the CLR stored procedure definition:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void CalculateResultByUser(SqlInt32 idUsuario, SqlDateTime fechaInicio, SqlDateTime fechaFin)
{
}

And this is how I am trying to call it:

EXEC CalculateResultByUser  5, '2014-07-26 00:00:00', '2014-07-26 23:59:59'

Any help please? Thanks Jaime

Answer 1

Sure enough, SqlInt32 doesn't appear to implement IConvertible.

I am guessing that somewhere in your stored procedure you are calling Convert.ToInt32 to extract the integer value from your SqlInt32 - which will not work because this struct does not implement this interface.

It does have an explicit conversion:

    int id = (idUsuario.IsNull ? -1 : (int)idUsuario);

Or alternatively

    int? nId = (idUsuario.IsNull ? (int ?)null : (int)idUsuario);

Can you use that?