Error converting varchar to numeric with MSSQL

Query

SELECT TOP 1000
CASE WHEN VRI.Square_Footage <> '' 
THEN VRI.Square_Footage 
ELSE 
    CASE WHEN VRI.Property_Type = 'LAND' 
        THEN CAST((CONVERT(NUMERIC(38, 3),VRI.Acres)*43560) AS DECIMAL) 
    ELSE 
        VRI.Lot_Size 
    END 
END
FROM View_Report_Information_Tables AS VRI

Even if I checked for VRI.Acres with isnumeric(), it still yield the same exact error? How can I 开发者_JAVA百科fix this problem?

ISNUMERIC doesn't guarantee that it will successfully cast to decimal.

SELECT ISNUMERIC('£100') /*Returns 1*/

SELECT CONVERT(NUMERIC(38, 3),'£100') /*Error*/

Additionally all branches of the case statement need to return compatible datatypes. It looks like VRI.Square_Footage is a string.

Does this work for you?

SELECT TOP 1000 CASE
                  WHEN ISNUMERIC(VRI.Square_Footage + 'e0') = 1 THEN
                  VRI.Square_Footage
                  WHEN VRI.Property_Type = 'LAND'
                       AND ISNUMERIC(VRI.Acres + 'e0') = 1 THEN CAST((
                  CONVERT(NUMERIC(38, 3), VRI.Acres) * 43560 ) AS DECIMAL)
                  WHEN ISNUMERIC(VRI.Lot_Size + 'e0') = 1 THEN VRI.Lot_Size
                END
FROM   View_Report_Information_Tables AS VRI  

Here run this

 SELECT ISNUMERIC('d25')

That can be converted to float but not decimal/numeric

SELECT CONVERT(FLOAT,'2d5')

As you can see, you can't depend on numeric for decimal/numeric data types

Take a look at IsNumeric, IsInt, IsNumber for some code that will show you how you can check