CODEWITHSUNDEEP
sql-servert-sql
Threadw
Threadw

Reputation: 1

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

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?

Upvotes: 0

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 2

Related Questions