Sql-server – Conversion failed when converting the nvarchar value ‘value’ to int

sql server

I have the following code to pull a number of values from several rows into a single column and pipe-delimit the values.

SELECT DISTINCT P2.PatientID, 
SUBSTRING(
    (
        SELECT '| '+ CAST(P1.ProcedureCode AS NVARCHAR(MAX))  AS [text()]
        FROM #Proc50 P1
        WHERE P1.PatientID = P2.PatientID
        ORDER BY P1.PatientID
        FOR XML PATH ('')
    ), 2, 1000) [ProcedureCodes]
INTO #Proc_Concat
FROM #Proc50 P2

I then have a dataset that looks like this:

PatientId ProcedureCodes

1111111 11100| 17271| 1CHKS| 1CHN1| 1CHNS| 36415| 51798| 69210| 70450| 70544

When I try to insert the values into the final table I receive the conversion error. I tried casting the value but that hasn't helped. I also created a table for insertion where the field was nvarchar.

I am using SQL Server 2018. Any help would be appreciated.

EDIT to answer questions:
Here is the definition of #Proc50

Both P1.PatientId and P2.PatientId are int

For the code that I am using to insert the results of the pipe-delimited procedure, I am creating a table for the insertion.

    CREATE TABLE dbo.tmpWellMedTest (
                    PatientId int not null
                    ,Lastname  varchar(30) not null
                    ,Firstname  varchar(30) not null
                    ,ProcedureCodes nvarchar(MAX) null
                    ,Diagnosis1 varchar(10) null
                    ,Diagnosis2 varchar(10) null
                    ,Diagnosis3 varchar(10) null)

Then the insertion script:

SELECT
 ME.PatientId
,ME.LastName
,ME.FirstName   
,P.ProcedureCodes
,ISNULL(ME.Diagnosis1,'')                AS Diagnosis1
,ISNULL(ME.Diagnosis2,'')                AS Diagnosis2
,ISNULL(ME.Diagnosis3,'')               AS Diagnosis3
FROM vwPatientDemographic ME
LEFT OUTER JOIN #Proc_Concat        P
    ON P.LbPatientId = P.ProcedureCodes

Best Answer

On of the following statements use implicit conversion from int to varchar:

 ON P1.PatientID = P2.LbPatientID
or
 ON P.LbPatientId = P.ProcedureCodes

We know that PatientID is int and ProcedureCodes is varchar. Type for LbPatientID is not clear from your post.

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

This is not clear what output is needed. You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server.

If you want something like 5 Hours 15 Minutes you can try this query:

[Time Spent(In hrs)] =
    CAST(
        SUM(
            DATEDIFF(hour, ld_time_tracker_start_time, ld_time_tracker_end_time)                     
        )
    as varchar(10))
    +' Hours '+ 
    CAST(
        SUM(
            DATEDIFF(minute, ld_time_tracker_start_time, ld_time_tracker_end_time) % 60       
        )
    as varchar(10))
+' Minutes'

Note that I have replaced HH by hour. This is easier to read and understand and it avoid mistakes. See DATEDIFF (Transact-SQL).

Best Answer

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

Related Question