Problem checking SQL values with WHEN statement

Question

I have this problem in SQL Server 2008. I have created a table to insert data and then the user will be able to check if the data is correct.

CREATE TABLE dbo.temp_horario2 (
idTempHorario INT IDENTITY(1,1) NOT NULL,
idEmpleado VARCHAR(500),
nroDocumento VARCHAR(500),
dia VARCHAR(500),
idHorario VARCHAR(500),
idUsuario INT
);

Few of those columns should not store VARCHAR values, but I have done it in that way so the user will be able to see a temporary table with all the information he will insert (in a masive upload with a csv file). The problem is that when I check the values of these columns, with the original values that are stored in other tables, I get these errors.

Msg 245, Level 16, State 1, Line 1 Error de conversión al convertir el valor varchar 'asdf' al tipo de datos smallint.

-

La conversión del valor varchar '234324234' ha desbordado una columna INT2. Utilice una columna de tipo integer mayor.

These are the inserts I am checking:

INSERT INTO dbo.temp_horario2 (idEmpleado, nroDocumento, dia, idHorario, idUsuario) VALUES (12, 3224, 1, 1, 1);
INSERT INTO dbo.temp_horario2 (idEmpleado, nroDocumento, dia, idHorario, idUsuario) VALUES (2123, 10782295, 1, 1, 1);
INSERT INTO dbo.temp_horario2 (idEmpleado, nroDocumento, dia, idHorario, idUsuario) VALUES (234324234, 3232495, 34, 2, 34);
INSERT INTO dbo.temp_horario2 (idEmpleado, nroDocumento, dia, idHorario, idUsuario) VALUES ('asdf', 2, 'dsf', 'dos', 34);

And this is the query that brigns me those errors.

SELECT th.idTempHorario, th.idEmpleado, th.nroDocumento, th.dia, th.idHorario, th.idUsuario,
CASE
    WHEN th.idEmpleado IN(SELECT rhem.idEmpleado FROM rrhh.dbo.Empleado AS rhem) THEN 1
    ELSE 0
END AS validacionIdEmpleado,
CASE
    WHEN th.nroDocumento IN(SELECT rhem.numTipoDocuIdent FROM rrhh.dbo.Empleado AS rhem) THEN 1
    ELSE 0
END AS validacionNroDocumento,
CASE
    WHEN th.dia IN(SELECT gd.idDia FROM General.dbo.dia AS gd) THEN 1
    ELSE 0
END AS validacionDia,
CASE
    WHEN th.idHorario IN(SELECT rhas.idHorarioAdmin FROM rrhh.asistencia.horarioAdmin AS rhas) THEN 1
    ELSE 0
END AS validacionIdHorario
FROM database.dbo.temp_horario2 AS th

……SELECT rhem.idEmpleado FROM rrhh.dbo.Empleado AS rhem… ?? — Luuk
– Luuk, Commented Sep 29, 2019 at 14:46

forpas · Accepted Answer · 2019-09-29 14:47:52Z

1

It seems that the subqueries you use after IN return resultsets containing integer values so when you compare a varchar column like idEmpleado (and the others) to them you get that error.
What you can do is cast these values to varchar like this:

SELECT cast(rhem.idEmpleado AS VARCHAR) FROM rrhh.dbo.Empleado AS rhem

See the demo.

answered Sep 29, 2019 at 14:47

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem checking SQL values with WHEN statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related