Conversion failed when converting the nvarchar value '2,3' to data type int Error

Question

When I run the stored procedure, an error occurs. Please suggest how to fix this error.

This is my stored procedure:

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

My First table is

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)
);

Secound Table is

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

What is the error? I also changed the tag to SQL Server, because this looks like SQL Server code. — Gordon Linoff, Feb 10 '20 at 11:44
Conversion failed when converting the nvarchar value '2,3' to data type int ...@GordonLinoff — Nikunj, Feb 10 '20 at 11:46
`login.hobby` isn't a comma separated list of hobby IDs, is it? If it is, check this: ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|189.8108) And then change your schema. — sticky bit, Feb 10 '20 at 11:50
Please **show us** the table structure (the `CREATE TABLE` statements) for both these tables, and show us some **sample data** that you have in those tables — marc_s, Feb 10 '20 at 11:59
@Nikunj . . . Your `JOIN` condition (`e.Hobby = c.Id`) is highly suspicious, but you have not provided enough information in the question to help anyone answer. — Gordon Linoff, Feb 10 '20 at 12:05
Seems like you're trying to compare the Hobby varchar value `'2,3'` with an integer. — jarlh, Feb 10 '20 at 12:14
@Nikunj I have made the query. Reopen this question so that i can post my answer. — Atk, Feb 10 '20 at 12:32
First split string see https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — P.Salmon, Feb 10 '20 at 12:42
@Atk no, don't repost questions, that will simply get the new question locked. — Thom A, Feb 10 '20 at 12:49
Users cannot post answers to closed questions, @Nikunj . You need to improve your question sufficiently so that it can then cycle through reopen queue successfully. — Thom A, Feb 10 '20 at 12:52
@Atk , if delet This Question , then I Cant Able to Post New Question — Nikunj, Feb 10 '20 at 12:53

score 0 · Accepted Answer · edited Feb 10 '20 at 12:48

0

ISNULL is the opposite to COALESCE, it will always return the type of first argument, so you should do conversions to VARCHAR. The error is because your JOIN clause tblHobby c ON e.Hobby = c.Id implicitlly tries to convert e.Hobby to an int to compare. As you have a delimited value in that column, the database fails to convert the value.

Try this:

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

edited Feb 10 '20 at 12:48

Thom A

88,727
11
45
75

answered Feb 10 '20 at 12:13

Leszek Mazur

2,443
1
14
28

it's Give All selected value in new raw , but i want I value in One Raw – Nikunj Feb 10 '20 at 12:26
Than you should specify which value do you want to see. – Leszek Mazur Feb 10 '20 at 12:29
sir, I want value Of 'Hobby' Field of table 'tblHobby' in Single Raw...........tblHobby c ON ',' + ISNULL(e.Hobby, '') + ',' LIKE '%,' + CAST(c.Id AS NVARCHAR(40)) + ',%' In this I get value In saparate Raw – Nikunj Feb 10 '20 at 12:34
2

*"ISNULL in oposite to COALESCE have always type of first argument"* this *implies* that `COALESCE` will return the data type of the last arguement; that isn't true. `COALESCE` is a shorthand `CASE` expression, and `CASE` uses data type precedence. `COALESCE(1,'0')` and `ISNULL(1,'0')` would both return a `int`, not a `varchar` and an `int` respectively. – Thom A Feb 10 '20 at 12:45
[Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15#return-types) – Leszek Mazur Feb 10 '20 at 13:32
More specific difference on second point: [DOCS](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15#comparing-coalesce-and-isnull): "Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence." – Leszek Mazur Feb 10 '20 at 13:41

Conversion failed when converting the nvarchar value '2,3' to data type int Error

1 Answers1