SQL Server – Resolving VARCHAR to Numeric Conversion Error

sql servertype conversionvarchar

I have the following schema/data in MSSQL Server 2019 (SQLFiddle):

CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

sales has mixed data i.e VARCHAR and NUMERIC. The transaction filter takes care of JOINing correctly.

Why does the following SELECT fail (For some reason beyond my control, the generated query has N literals for strings.)?

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N'number' 
WHERE 
    products.idn in (1);

I don't see why casting NVARCHAR to NUMERIC is an issue:

SELECT CAST (N'1' as NUMERIC);

If I modify the query slightly it works:

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N 'number' 
WHERE
    -- Selecting the same data from `sales`.
    sales.pid in (1);

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number' 
WHERE 
    products.idn in (1);

Best Answer

A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'

The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.

Related Question