Avoiding Conversion errors

Question

Post reply

Avoiding Conversion errors

Chrissy321

SSCoach

Points: 15768

November 2, 2023 at 12:12 am

#4319296

Hello All,

Couple of questions on conversion errors.

First I have to convert since my reference table stores numeric and character data.

Is it bad practice to allow implicit conversions?

Why does Convert to INT intermittently fail with a conversion error? I can't replicate this with my limited data but it is happening on my real data. Is it dependent on execution plan? Obviously there is mixed data in ReferenceValue.

Is Convert to VARCHAR the best approach since there isn't mixed data in an INT column?

DROP TABLE IF EXISTS #GenericReferenceTable
CREATE TABLE #GenericReferenceTable(ReferenceType VARCHAR(50),ReferenceValue VARCHAR(50))
INSERT INTO #GenericReferenceTable (ReferenceType,ReferenceValue) VALUES ('Type1','100')
INSERT INTO #GenericReferenceTable (ReferenceType,ReferenceValue) VALUES ('Type2','A')

DROP TABLE IF EXISTS #Things CREATE TABLE #Things(ThingID INT)
INSERT INTO #Things (ThingID) VALUES (100)
INSERT INTO #Things (ThingID) VALUES (200)
INSERT INTO #Things (ThingID) VALUES (300)

--Implicit converion
SELECT * FROM #Things WHERE ThingID IN (SELECT ReferenceValue FROM #GenericReferenceTable WHERE ReferenceType = 'Type1') 
--Convert to INT
SELECT * FROM #Things WHERE ThingID IN (SELECT CONVERT(INT,ReferenceValue) FROM #GenericReferenceTable WHERE ReferenceType = 'Type1') 
--Convert to VARCHAR
SELECT * FROM #Things WHERE CONVERT(VARCHAR(50),ThingID) IN (SELECT ReferenceValue FROM #GenericReferenceTable WHERE ReferenceType = 'Type1')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

You have a VARCHAR column containing string data. Some of the data can be converted to an INT and some cannot. What do you want to see in the cases where the conversion fails?

You may want to look into TRY_CAST. It will return NULL in the event of a failed conversion, rather than an error.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Chrissy321 SSCoach Points: 15768 More actions · Answer 2

I only try to convert when I know the data in ReferenceValue is convertible to INT by using the WHERE ReferenceType = clause.

Currently the conversion sometimes fails even when I am limiting the ReferenceType to data that should be able to convert to INT. Its as if the engine ignores the WHERE clause, scans the whole column and generates the error because all the column data can't be converted even though the limited data returned by the WHERE clause can be converted.

ScottPletcher SSC Guru Points: 101124 More actions · Answer 3

SQL may not evaluate things in the order you expect it to, so something like that could indeed be happening in certain circumstances.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

I suggest you create a query which includes a TRY_CAST to INT of the column of interest.

Use your existing WHERE clause, but add another condition

AND TRY_CAST(Col as INT) IS NULL

This should help you find any unexpected data.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Chrissy321 SSCoach Points: 15768 More actions · Answer 5

Yes, adding a date filter to my where clause seems to create a new plan causing the conversion error.

The Convert to VARCHAR seems reliable but imposes a performance penalty.

A better design for a generic reference table like this is probably something like.

CREATE TABLE #GenericReferenceTable(

ReferenceType VARCHAR(50),

ReferenceValueChar VARCHAR(50)),

ReferenceValueInt INT,

ReferenceValueDate DATE

)

Then based on ReferenceType you could pick the appropriate column and avoid conversions.

TRY_CAST doesn't help me here since I already know there is data in the column that will not convert.

Thanks all.

ScottPletcher SSC Guru Points: 101124 More actions · Answer 6

Or make the value column a sql_variant data type.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".