问题描述:

I am getting an error in SQL Server:

Conversion failed when converting the nvarchar value 'Null' to data type int.

In my employee table I have:

ID Int,

Name nvarchar(50),

Gender nvarchar(10),

Salary nvarchar(50),

DepartmentId nvarchar(50)

Nulls are allowed only for DepartmentId.

In my Department table I have:

Id int,

Department Name nvarchar(50),

Location nvarchar(50),

DepartmentHeead nvarchar(50)

None of them allow nulls.

I am trying to join the tables as so:

Select Name, Gender, Salary, DepartmentName

From Employee

JOIN Department ON Employee.DepartmentId = Department.Id

That's when I get the error:

Conversion failed when converting the nvarchar value 'Null' to data type int.

I am not understanding the error here. My goal is to join the DepartmentName column with the Employee table.

网友答案:
  1. If you're storing a foreign key reference to another table (in this case, DepartmentId on the Employee table to the Id field on the Department table), they need to be of the same data type. If you don't have the foreign key reference defined, you should. It will help ensure referential integrity and prevent errors like this.

  2. When storing null in a field, you need to be sure you're storing an actual null value rather than a string containing "Null" (which is what it looks like you're doing in this case). It's that conversion that is causing the error.

网友答案:

The DepartmentId column in the Employee table should have been defined as an INT. Because it is the Foreign Key of the ID column in the Department table - which is defined as an INT.

Your employee table should look like this:

CREATE TABLE Employee
(
    ID Int, 
    Name nvarchar(50),
    Gender nvarchar(10), 
    Salary nvarchar(50), 
    DepartmentId INT
)

The DepartmentId column needs to be an INT, because it joins to the ID column of the Department table which is defined as an INT.

It seems like you may have some string values in the DepartmentId column with the word 'null'. These are not nulls but strings. You will need to convert them to NULLs first, using something like this:

update employee set departmentid = null where lower(departmentid) like '%null%'
网友答案:

I think your query should be like this:

SELECT Name, Gender, Salary, DepartmentName
FROM Employee
JOIN Department ON Employee.DepartmentId = CONVER(nvarchar(50), Department.Id)

And if your goal is really join the DepartmentName column with the Employee table:

SELECT Name, Gender, Salary, DepartmentName
FROM Employee
JOIN Department ON Employee.DepartmentId = Department.DepartmentName
相关阅读:
Top