T-SQL :: Error converting data type varchar to numeric when changing 1 to 0.5

I’m writing a complex T-SQL query with CASE that will help me calculate how much time it will take me to migrate databases based on the size of each database:

SELECT 
    ProductVersion
    ,CompatibilityLevel
    ,convert(numeric(10,2), substring([Size], 1, (len([Size]) - 3))) as [Size in MB]
    ,CASE
        when ProductVersion < '11%' THEN 1
        when ProductVersion = NULL then 1
        ELSE ''    
    END  + 
    CASE
        when CompatibilityLevel < '110' then 1
        when CompatibilityLevel = NULL then 1
        ELSE ''
    END  + 
    CASE
        when  convert(numeric(10,2), substring([Size], 1, (len([Size]) - 3))) < 100.00 THEN 1 -- Here is the problem
        ELSE ''
    END AS [Hours of work]
FROM MyDatabaseList

All good, it works when I set 1 hour of work for every database which has less then 100.00 MB.

Also all other values are summed and in the Hours of work column you can see numbers like 0, 1, 3, 2… This means SQL Server is calculating values. All good.

But well, telling to my manager that I have to work 1 hour for a database that only has 100.00 MB of size is ridiculous.

Let’s change the line:

    when  convert(numeric(10,2), substring([Size], 1, (len([Size]) - 3))) < 100.00 THEN 1

and let’s put 0.5 instead of 1:

    when  convert(numeric(10,2), substring([Size], 1, (len([Size]) - 3))) < 100.00 THEN 0.5

…and bang – I get this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Questions:

1. Why this error now?
2. Why it was successfully calculating before and now needs a datatype somewhere?
3. What change do I have to add to my query?
4. Why it says on Line 1? The error must be on Line 16, right?

Answer

The error is telling you the conversion is happening in the statement that starts in line 1.

As for why, it’s because '' cannot be converted to a numeric but it can be converted to an int.

SELECT CONVERT(decimal(2,1),'')
--Error converting data type varchar to numeric.
GO
SELECT CONVERT(int,'')
--
GO

You were previously converting it to an int (the literal 1 is an int). Get out of the habit of declaring '' for a number; it is by definition not a number, it is a zero length string. If you want zero, then use 0.