-1

I have following function on SQL Server and LocalDB:

CREATE FUNCTION [dbo].[ConvertToLong]
(
    @Value Varchar(200)
)
RETURNS Bigint
AS
BEGIN
    If ISNUMERIC(@Value)<>1
    Return 0
    Else
    Return Cast(@Value as bigint)

    Return 0
END

when calling this function like this:

SELECT dbo.[ConvertToLong]('13668433347')

I receive different results:

SQLServer: 13668433347

LocalDB: Arithmetic overflow error converting expression to data type int.

Any ideas what is the problem and how to fix it for LocalDB?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Piotr Auguscik
  • 3,651
  • 1
  • 22
  • 30
  • 1
    There's no difference. The error complains about `int` not bigint. Is there a typo perhaps? – Panagiotis Kanavos Sep 01 '20 at 08:34
  • Note the error: "converting expression to data type *int*". Make very sure you haven't overlooked a simple difference *you* introduced. – Jeroen Mostert Sep 01 '20 at 08:35
  • 1
    Side note, don't use `isnumeric`; it's a terrible function. Just use `TRY_CONVERT`. Something like `SELECT dbo.ConvertToLong('1.23')` is going to cause your function to error. Also, why do you have a wayward `RETURN 0` at the end of the function? – Thom A Sep 01 '20 at 08:38
  • @PanagiotisKanavos There is no typo I have copied the create statement just to be sure that there is no difference. – Piotr Auguscik Sep 01 '20 at 08:59
  • But why not just do `SELECT TRY_CONVERT(bigint,'13668433347');` @PiotrAuguscik ? Why rely on a flawed function (`ISNUMERIC`)? There's not need for a user defined scalar function here; you're simply reinventing the wheel. – Thom A Sep 01 '20 at 09:01
  • @JeroenMostert THe problem is related somehow to if statement - without it cast works. It seems that somehow if block forces conversion to int (at least this is my current understanding) – Piotr Auguscik Sep 01 '20 at 09:02
  • @Larnu 1. Because I need it as function for EF. 2. I would like to know the source of issue – Piotr Auguscik Sep 01 '20 at 09:03
  • Entity Frame work can use built in T-SQL Functions, and EF2 has it's *own* functions to convert a `String` to a `Long`. – Thom A Sep 01 '20 at 09:04
  • @Larnu I have .NET 4.7 and EF 6 – Piotr Auguscik Sep 01 '20 at 09:05
  • So, what's wrong with the [`Convert.ToInt64` Method](https://learn.microsoft.com/en-us/dotnet/api/system.convert.toint64?view=netcore-3.1)? Why do this in SQL Server if you want to do this in Entity Frame work? Why use a flawed user function if you "*must*" do it in SQL Server and not the function `TRY_CONVERT` which is available in alll **supported** versions of SQL Server (including those only in extended support). This has a smell of an [XY Problem](http://xyproblem.info). – Thom A Sep 01 '20 at 09:08
  • @Larnu Convert.ToInt64: System.NotSupportedException: LINQ to Entities does not recognize the method 'Int64 ToInt64(System.String)' method, and this method cannot be translated into a store expression. – Piotr Auguscik Sep 01 '20 at 09:15
  • Seems like *that* is the question you should be asking, @PiotrAuguscik . – Thom A Sep 01 '20 at 09:16
  • Does this answer your question? [Convert.Int64 Is Not Reconized LINQ To Entities](https://stackoverflow.com/q/10095276/2029983) – Thom A Sep 01 '20 at 09:17
  • @Larnu I am already using methods that ARE available in SQL Server but the problem is that it doesn't work the same way. Yes, `TRY_CONVERT` works because it allows me to ommit `if` BUT it does not explain why the if enforces cast to int. – Piotr Auguscik Sep 01 '20 at 09:18
  • @Larnu no it doesn't because the value that needs conversion is in SQL not memeory. – Piotr Auguscik Sep 01 '20 at 09:19
  • 1
    I am actually surprised that the question got downvotes. The OP is specific on what they are doing, unlike many other. – Thom A Sep 01 '20 at 10:14
  • 1
    What are the differences in `SELECT @@VERSION, compatibility_level FROM sys.databases WHERE database_id = DB_ID()` between the instances? If there is a behavioral difference between the databases while using the exact same function definition, my money is on a bug in scalar UDF inlining (introduced in SQL Server 2019). If this hunch is correct, you can use `WITH INLINE = OFF` in the function definition to disable it. Otherwise, an unrelated problem in plan generation is still a possibility, but less likely. – Jeroen Mostert Sep 01 '20 at 10:29
  • yeah, looks very like an inline scalar UDF issue, `Return 0` uses a literal of type `int` so chances are that somewhere it does an incorrect `CONVERT_IMPLICT` to `int` - probably this issue is already fixed in a CU – Martin Smith Sep 01 '20 at 11:30
  • 3
    reproed on 15.0.2000.5 - the problem is that it gets inlined as `CASE WHEN CASE WHEN (0) = (0) THEN (1) ELSE CASE WHEN (0) = (1) THEN (1) ELSE (0) END END = (0) THEN (0) ELSE CONVERT_IMPLICIT(int,CASE WHEN (0) = (0) AND CASE WHEN (0) = (1) THEN (1) ELSE (0) END = (0) THEN (13668433347) ELSE CONVERT_IMPLICIT(bigint,CASE WHEN (0) = (1) THEN (0) ELSE NULL END,0) END,0) END` - No longer repros on 15.0.4013.40 RTM-CU2 so fixed in CU1 or 2 – Martin Smith Sep 01 '20 at 11:39
  • @Larnu - I imagine the downvotes are because people think that the OP must be describing things incorrectly due to the message about casting to `int` - whereas this issue is perfectly reproducible as a scalar UDF inlining bug. A common problem on SO is that people just dismiss issues they don't understand rather than trying to understand them. – Martin Smith Sep 01 '20 at 11:54
  • Seems like, @MartinSmith , that good updates management would have avoided this problem in the first place. :) – Thom A Sep 01 '20 at 15:15

1 Answers1

-1

The problem here is you are trying to reinvent the wheel. Every supported version of SQL Server has the function TRY_CONVERT which returns NULL if the value cannot be converted.

ISNUMERIC is also very flawed. Things like ISNUMERIC('1.23') and ISNUMERIC('7e2') return 1, however, both CONVERT(int, '1.23') and CONVERT(decimal(10,2),'7e2') fail. Also ISNUMERIC('') returns 0, however, CONVERT(tinyint,'') returns 0, not an error.

As a result, the answer is use the inbuilt function, TRY_CONVERT (or TRY_CAST):

SELECT TRY_CONVERT(bigint,'13668433347');

If you want to return 0 in the event of a conversion error you can use ISNULL and if you want to retain NULL values a CASE expression. So something like:

SELECT CASE WHEN YourColumn IS NOT NULL THEN ISNULL(TRY_CONVERT(bigint,YourColumn),0) END;

There are plenty of articles on why ISNUMERIC should be avoided as well:

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • It would be great to understand why this answer is not helpful (Piotr Auguscik, as your reputation went down by 1, I can deduce it was very likely yourself), and I will be more than happy to improve it. I've added multiple articles on why `ISNUMERIC` is bad, if you don't believe the brief outline I give in the answer. – Thom A Sep 01 '20 at 09:31
  • I know which values are passed to this method - no digits after comma - so no `ISNUMERIC` is not a problem event if it is not adiviced to use the question is not abouit it but thats your only focus here. No I am not trying to reinvent the wheel - this starting sentence only shows how rude you are and not trying at all understand the core issue of different behaviour between sql server versions. – Piotr Auguscik Sep 01 '20 at 09:36
  • Using a user function to convert a `varchar` to a `bigint`, while implementing `ISNUMERIC` instead of using a built in function is exactly what reinventing the wheel is, @PiotrAuguscik . It's not rude, it's a simple fact I am afraid. I am sorry if you feel that it's being rude, however, it's simply not. If you do, however, truly feel that statement is "rude" then you can flag by answer as *"**rude or abusive**"* and a moderator van review it. – Thom A Sep 01 '20 at 09:40