Conversion failed when converting the varchar value to data type int in sql query

Question

I'm trying a dynamic SQL query but I'm getting an error about conversion but can't figure out what the problem is.

Here's my query :

DECLARE @VarSubCriBusqueda VARCHAR(MAX)
SET @VarSubCriBusqueda = 'Marca'

DECLARE @InputString VARCHAR(MAX)
SET @InputString = '%'

SELECT 
    Marcas.MarcaID, Marcas.Marca, Clases.ClaseNo, Marcas.Codigo, Propietarios.Propietario
FROM 
    Marcas 
LEFT OUTER JOIN 
    Propietarios ON marcas.PropietarioID = Propietarios.PropietarioID
LEFT OUTER JOIN
    Clases ON Marcas.ClaseIntID = clases.ClaseID 
LEFT OUTER JOIN
    Clientes ON Clientes.ClienteID = Marcas.ClienteID
WHERE 
    CASE @VarSubCriBusqueda
        WHEN 'Clases.ClaseNo'
            THEN Clases.ClaseNo
        WHEN 'Codigo'
            THEN Codigo
        WHEN 'Propietarios.Propietario'
            THEN Propietarios.Propietario
        WHEN 'Clientes.Empresa'
            THEN Clientes.Empresa
        WHEN 'Contacto'
            THEN Marcas.Contacto
        WHEN 'Marca'
            THEN Marca
    END LIKE @InputString

It end with the following message:

Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'ACADEMY AWARDS' to data type int.

Any help?

What is your WHERE clause supposed to be doing? Most likely, a CASE expression is not what you need there. — Tim Biegeleisen
Please add the DDL for the tables to your question so that we can see the data types involved. — HABO

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-11-13T18:33:55

In all likelihood. you are missing the fact that a case expression returns a single type. And, if any of the then or else clauses are numbers, then all are converted to numbers. I imagine that one or more of the columns are numbers. One fix is explicit conversion.

A better fix is to get rid of the case expression entirely:

WHERE (@VarSubCriBusqueda = 'Clases.ClaseNo' AND Clases.ClaseNo like @InputString) OR
      (@VarSubCriBusqueda = 'Codigo' AND Codigo like @InputString) OR
       . . .

And even with this formulation, you should use the correct comparison for your type. So, if you have a number, LIKE doesn't seem appropriate.

Conversion failed when converting the varchar value to data type int in sql query

1 Answers