0

I am using a sql_variant field at MS SQL Server 2019. Select queries containing a WHERE < clause are not working when inserting a number as real or float. It does work when insering as numeric. Not working means, smaller values were not selected.

Unfortunately there is no C# numeric sql type, but only SqlDbType.Float or SqlDbType.Real.

Any idea how use sql_variant as float or real in combination with WHERE smaller statements?

Baumfaust
  • 31
  • 5
  • 1
    variant is one of those things that exists for compatibility with old systems, but shouldn't really be used in new development. – Joel Coehoorn Dec 09 '21 at 15:07
  • 1
    `SQL_VARIANT` is only very rarely a proper solution to a problem. You should certainly be able to pass a `NUMERIC` as a variant, though -- use `SqlDbType.Variant` and pass a `decimal`. You should arguably use `Variant` even when you are passing a `float` or `double`, if that is the type on the server side. – Jeroen Mostert Dec 09 '21 at 15:07
  • There's very few times that `float` and `real` is the right choice for your data as well. I think taking a step back and explaining what you're really trying to achieve here would be a much better idea. – Thom A Dec 09 '21 at 15:24
  • You might have multiple issues here and I suggest you focus on one. Perhaps focus on retrieving information from the database first since resistance to redesigning the table is likely. A [MCVE](https://stackoverflow.com/help/minimal-reproducible-example) is highly recommended here. What values are actually stored in the table and how exactly do you retrieve them in your code? – SMor Dec 09 '21 at 15:29
  • @JeroenMostert Converting my C# double value to C# decimal and then passing it as SqlDbType.Variant works for me. Thank you very much. – Baumfaust Dec 09 '21 at 15:39
  • `WHERE <` also doesn't really make sense with a `variant`, you may as well `TRY_CAST` it – Charlieface Dec 09 '21 at 16:41

0 Answers0