November 2, 2023 at 12:12 am
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')
November 2, 2023 at 9:13 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 2, 2023 at 2:30 pm
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.
November 2, 2023 at 3:14 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
November 2, 2023 at 3:18 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 2, 2023 at 3:28 pm
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.
November 2, 2023 at 5:15 pm
Or make the value column a sql_variant data type.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply