SQL Server checking if numeric still throws error

Question

I have an odd problem in SQL Server. Basically we have a generic type object with two columns, Description 1 and Description 2. We check if Description2 is a number and if it is, check the value/use it. However I am getting the following error when attempting to run the query.

Conversion failed when converting nvarchar value 'Test Other' to data type int.

Now normally I would simply assume that it was attempting to join on improper values/etc, but the varchar it's saying in the error should not be valid from the ISNUMERIC.

Here is a sample of my query:

SELECT *
FROM base_table
JOIN org_type o ON o.id = base_table.org_id
LEFT JOIN generic_object go ON go.id = base_table.org_id AND ISNUMERIC(description2)
WHERE description2 = @orgId
  AND base_table.id = @baseId

What is strange to me, when I remove the last line from the query, I do not receive an error.

How have you defined @baseId? Tell us both the datatype and value. — David Tansey
– David Tansey, Commented Aug 31, 2016 at 18:44
For the purpose of this, the value is variable but in my system they are currently set to 2535 and 2357 respectively. — Sh4d0wsPlyr
– Sh4d0wsPlyr, Commented Aug 31, 2016 at 18:44
Do you have any idea where the reported problem value 'Test Other' is coming from? — David Tansey
– David Tansey, Commented Aug 31, 2016 at 18:45
Yes, we have multiple types of "entities". The one I am attempting to JOIN with is an Organization, 'Test Other' is from the "Other Entity" type. I don't know why it is there other then the fact that it is related to the base_table. On that note - "base_table" is a many-to-many table of basically one type of ID to entities. — Sh4d0wsPlyr
– Sh4d0wsPlyr, Commented Aug 31, 2016 at 18:51
We eventually realized what the issue was. So simple it was bad. description2 is a varchar. The program we were using to test the scripts was automatically casting it properly, the clients was not. — Sh4d0wsPlyr
– Sh4d0wsPlyr, Commented Sep 20, 2016 at 16:46

xQbert · Accepted Answer · 2016-08-31 19:02:14Z

2

Use an inline view to return only numeric description2 records to start with. This way, it has to resolve a data set only containing numeric values first.

SELECT *
FROM base_table
JOIN org_type o ON o.id = base_table.org_id
LEFT JOIN (SELECT * 
           FROM generic_object 
           WHERE isNumeric(description2)) go ON go.id = base_table.org_id
WHERE description2 = @orgId
    AND base_table.id = @baseId

answered Aug 31, 2016 at 19:02

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2016-08-31 20:08:20Z

2

I haven't ever seen this kind of join... but I think this will get you the desired result. First remove the ISNUMERIC from the join. I basically compare the variable to description2 only if it is numeric, and if it isn't then just compare it to itself which is always true.

WHERE ISNULL(@orgId,'') = case when ISNUMERIC(description2) = 1 then description2 else ISNULL(@orgId,'') end

edited Aug 31, 2016 at 20:08

answered Aug 31, 2016 at 19:02

S3S

25.2k5 gold badges29 silver badges48 bronze badges

2 Comments

Sh4d0wsPlyr Over a year ago

This seems to work for me, there was some other query issues that this showed me at the same time but the general idea worked perfectly and allowed me to advance. Thanks

S3S Over a year ago

No worries at all @Sh4d0wsPlyr

SS_DBA · Accepted Answer · 2016-08-31 18:51:46Z

0

To me it looks like the description2 = @orgId is the problem. If you are checking for ISNUMERIC(description2) on the JOIN, the other results you are getting will not be Numeric for that column. So the Where clause is giving you the error. Try moving the ISNUMERIC(description2) = 1 to the Where clause.

answered Aug 31, 2016 at 18:51

SS_DBA

2,4211 gold badge13 silver badges15 bronze badges

1 Comment

Sh4d0wsPlyr Over a year ago

Attempted this - same issue.

Collectives™ on Stack Overflow

SQL Server checking if numeric still throws error

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related