时间戳

首页 > 解决方案 > 将 nvarchar 值“2,3”转换为数据类型 int 时转换失败错误

sql - 将 nvarchar 值“2,3”转换为数据类型 int 时转换失败错误

问题描述

当我运行存储过程时，会发生错误。请建议如何解决此错误。

这是我的存储过程：

ALTER PROCEDURE [dbo].[spGetAllEmployees]    
AS
BEGIN
    SELECT
        ISNULL(e.Id, '') AS [Id], 
        ISNULL(e.FirstName, '') AS [First Name],
        ISNULL(e.LastName, '') AS [LastName],
        ISNULL(e.Gender, '') AS [Gender], 
        ISNULL(e.DOB, '') AS [DOB],
        ISNULL(c.Hobby, '') AS [Hobby],
        ISNULL(e.Photo, '') AS [Photo],
        ISNULL(e.City, '') AS [City]
    FROM 
        Login e
    INNER JOIN
        tblHobby c ON e.Hobby = c.Id
END

我的第一张桌子是

CREATE TABLE [dbo].[Login] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [FirstName] VARCHAR (30)   NULL,
    [LastName]  VARCHAR (30)   NULL,
    [Gender]    VARCHAR (6)    NULL,
    [DOB]       DATETIME       NULL,
    [Hobby]     NVARCHAR (50)  NULL,
    [Photo]     NVARCHAR (MAX) NULL,
    [City]      VARCHAR (50)   NULL,
    CONSTRAINT [PK_Login] PRIMARY KEY CLUSTERED ([Id] ASC)
);

第二张桌子是

CREATE TABLE [dbo].[tblHobby] (
    [Id]    INT           IDENTITY (1, 1) NOT NULL,
    [Hobby] NVARCHAR (50) NULL,
    CONSTRAINT [PK_tblHobby] PRIMARY KEY CLUSTERED ([Id] ASC)
);

标签： sqlsql-server

解决方案

ISNULL与 相反COALESCE，它将始终返回第一个参数的类型，因此您应该转换为VARCHAR. 该错误是因为您的JOIN子句tblHobby c ON e.Hobby = c.Id隐式地尝试转换e.Hobby为int要比较的。由于您在该列中有一个分隔值，因此数据库无法转换该值。

尝试这个：

tblHobby c ON ',' + ISNULL(e.Hobby, '') + ',' LIKE '%,' + CAST(c.Id AS NVARCHAR(40)) + ',%'

推荐阅读