在存储过程中将nvarchar值转换为数据类型int时转换失败

本文介绍了在存储过程中将nvarchar值转换为数据类型int时转换失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好， 我已经打了好几天了。我正在使用SQL SERVER v17.5 我尝试了很多来自互联网的建议和解决方案，但我似乎无法摆脱这个错误。在我尝试添加Part.Note列（根据新业务要求）之前，此过程一直运行良好。它作为nvarchar（500）存储在源表和我正在使用的每个其他表中。我尝试过COLLATION，CAST，CONVERT，ISNUMERIC（），甚至是单独的更新声明！似乎没什么用。 我在这里结束了我的智慧。有人可以对此有所了解吗？我查看了与CLR相关的内容，并且不确定它是否与MERGE STATEMENT和/或它试图达到的服务器有关。请帮帮忙！ 提前谢谢:) 这是我的疑问：= Hello All,I have been fighting this for a few days now. I'm using SQL SERVER v17.5I have tried many suggestions and solutions from across the internet but I can't seem to get rid of this error. This procedure has been running flawlessly until I try to add Part.Note column (as per new business requirement). It is stored as nvarchar(500) in the source table and every other table that I am working with. I have tried COLLATION, CAST, CONVERT, ISNUMERIC(), even a separate update statement! Nothing seems to work. I am at my wit's end here. Can someone shed a light on this? I looked at something related to CLR's and not sure if it has anything do with the MERGE STATEMENT and/or the server it is trying to reach. Please help!Thank you in advance :)This is my query:=USE [SomeDB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_RegionalPart]@RegionID as int ASBEGINDeclare @sql as nvarchar(max)Declare @sqltrunc as nvarchar(max)Declare @dbase as nvarchar(100)if @RegionID = 1 Select @dbase = 'USE NorthAmericanDW'if @RegionID = 2Select @dbase = 'USE SouthAmericanDW'if @RegionID = 3 Select @dbase = 'USE EuropeanDW'if @RegionID = 4Select @dbase = 'USE AsianPacificDW'If OBJECT_ID('tempdb.dbo.#tmp') is not null drop table tempdb.dbo.#tmpCreate Table #tmp([PlantID] [INT] NULL,[PartID] [int] NULL,[PartNo] [nvarchar] (100) NULL,[Col1] [int] NULL,[Col2] [int] NULL,[Col3] [int] NULL,[PartNote] [nvarchar] (500) NULL,[Col4] [int] NULL,[Col5] [float] NULL,[Col6] [nvarchar](20) NULL,[Col7] [datetime] NULL,[Col8] [int] NULL,[Col9] [varchar](1) NULL,[Col10] [float] NULL,[CurrentRecord] [bit] NULL)PRINT 'Temp Table Created'Select @sqltrunc = 'Truncate Table ' + Replace(@dbase,'USE ',Space(0)) + '.dbo.RegionalPart'exec(@sqltrunc)PRINT 'Table Truncated'Select @Sql = @dbase + space(1) +'SELECT PlantID,PartID,PartNo,Col1,Col2,Col3,part.Note,Col4,Col5,Col6,Col7,Col8,Col9,Col10,CurrentRecordFROM PartTABLE partINNER JOIN 1INNER JOIN 2INNER JOIN 3INNER JOIN 4INNER JOIN 5LEFT JOIN 1WHERE 1=1 AND join2.regionid = ' + cast(@RegionID as nvarchar(1)) PRINT @sqlInsert Into #tmp exec sp_executesql @sqlPRINT 'Insert values in #Tmp'Select @sql = @dbase + space(1) +'MERGE dbo.RegionalPart AS TARGET USING #tmp AS SOURCE ON (TARGET.PlantID = SOURCE.PlantID and TARGET.PartID = SOURCE.PartID ) WHEN MATCHED AND (TARGET.[PartNo] <> SOURCE.[PartNo] ORTARGET.[Col1] <> SOURCE.[Col1] ORTARGET.[Col2] <> SOURCE.[Col2] ORTARGET.[Col3] <> SOURCE.[Col3] ORTARGET.[PartNote] <> SOURCE.[PartNote],TARGET.[Col4] <> SOURCE.[Col4] ORTARGET.[Col5] <> SOURCE.[Col5] ORTARGET.[Col6] <> SOURCE.[Col6] ORTARGET.[Col7] <> SOURCE.[Col7] ORTARGET.[Col8] <> SOURCE.[Col8] ORTARGET.[Col9] <> SOURCE.[Col9] ORTARGET.[Col10] <> SOURCE.[Col10])THEN UPDATE SET TARGET.[PartNo] = SOURCE.[PartNo] ,TARGET.[Col1] = SOURCE.[Col1] ,TARGET.[Col2] = SOURCE.[Col2] ,TARGET.[Col3] = SOURCE.[Col3] ,TARGET.[PartNote] = SOURCE.[PartNote],,TARGET.[Col4] = SOURCE.[Col4] ,TARGET.[Col5] = SOURCE.[Col5] ,TARGET.[Col6] = SOURCE.[Col6] ,TARGET.[Col7] = SOURCE.[Col7] ,TARGET.[Col8] = SOURCE.[Col8] ,TARGET.[Col9] = SOURCE.[Col9] ,TARGET.[Col10] = SOURCE.[Col10]WHEN NOT MATCHED BY TARGET THEN INSERT ([PlantID],[PartID],[PartNo],[Col1],[Col2],[Col3],[part.Note],[Col4],[Col5],[Col6],[Col7],[Col8],[Col9],[Col10],[CurrentRecord])VALUES ( SOURCE.[PlantID] ,SOURCE.[PartID] ,SOURCE.[PartNo] ,SOURCE.[Col1] ,SOURCE.[Col2] ,SOURCE.[Col3] ,SOURCE.[part.Note] ,SOURCE.[Col4] ,SOURCE.[Col5] ,SOURCE.[Col6] ,SOURCE.[Col7] ,SOURCE.[Col8] ,SOURCE.[Col9] ,SOURCE.[Col10] ,SOURCE.[CurrentRecord]);'PRINT 'INSERT STATEMENT: ' PRINT @SQLexec sp_executesql @sqlPRINT 'INSERT STATEMENT AFTER EXECUTION: ' PRINT @SQLDrop Table #tmpEnd b $ b 我尝试了什么： 我试过： CONVERT CAST ISNUMERIC COLLATION 注 - >该特定字段具有来自不同语言的特殊字符拼音。我不确定为什么SQL无法识别类似于UTF-8格式的东西。我们在其他表中有很多字段，它们可以正常工作和显示数据。What I have tried:I have tried:CONVERTCASTISNUMERICCOLLATIONNOTE --> This specific field has special characters phonetic from different languages. I am not sure why SQL cannot recognize this in something similar to UTF-8 format. We have many fields like that in other tables and they work and display data fine.推荐答案 解决方案1已经纠正了代码中的两个基本错误但尚未完成特别是通过移动 OR 来清楚。以下是您正在生成的语法错误的修复。 在您的WHEN MATCHED子句中，您有Solution 1 has corrected the two fundamental errors in your code but has not made it particularly clear especially by moving the ORs. Here is the fix for the syntax errors you are generating.In your WHEN MATCHED clause you haveTARGET.[PartNote] <> SOURCE.[PartNote],它应该是TARGET.[PartNote] <> SOURCE.[PartNote] OR在你的THEN UPDATE子句中你有In your THEN UPDATE clause you have ,TARGET.[PartNote] = SOURCE.[PartNote],最后一个逗号不应该存在，因为你的代码是有效的 ... = SOURCE。[PartNote] ,, TARGET。[Col4] = SOURCE。[Col4] 发现另一个......你的WHEN NOT MATCHED条款（你在两个地方）That final comma should not be there as your code is effectively ...= SOURCE.[PartNote] ,, TARGET.[Col4] = SOURCE.[Col4] Spotted another one ... on your WHEN NOT MATCHED clause you have (in two places) [part.Note],那应该是[PartNote], MERGE dbo.RegionalPart AS TARGET USING #tmp AS SOURCE ON (TARGET.PlantID = SOURCE.PlantID and TARGET.PartID = SOURCE.PartID ) WHEN MATCHED AND ( TARGET.[PartNo] <> SOURCE.[PartNo] OR TARGET.[Col1] <> SOURCE.[Col1] OR TARGET.[Col2] <> SOURCE.[Col2] OR TARGET.[Col3] <> SOURCE.[Col3] OR TARGET.[PartNote] <> SOURCE.[PartNote] OR TARGET.[Col4] <> SOURCE.[Col4] OR TARGET.[Col5] <> SOURCE.[Col5] OR TARGET.[Col6] <> SOURCE.[Col6] OR TARGET.[Col7] <> SOURCE.[Col7] OR TARGET.[Col8] <> SOURCE.[Col8] OR TARGET.[Col9] <> SOURCE.[Col9] OR TARGET.[Col10] <> SOURCE.[Col10])THEN UPDATE SET TARGET.[PartNo] = SOURCE.[PartNo],TARGET.[Col1] = SOURCE.[Col1] ,TARGET.[Col2] = SOURCE.[Col2] ,TARGET.[Col3] = SOURCE.[Col3] ,TARGET.[PartNote] = SOURCE.[PartNote],TARGET.[Col4] = SOURCE.[Col4] ,TARGET.[Col5] = SOURCE.[Col5] ,TARGET.[Col6] = SOURCE.[Col6] ,TARGET.[Col7] = SOURCE.[Col7] ,TARGET.[Col8] = SOURCE.[Col8] ,TARGET.[Col9] = SOURCE.[Col9] ,TARGET.[Col10] = SOURCE.[Col10]WHEN NOT MATCHED BY TARGET THEN INSERT ([PlantID],[PartID],[PartNo],[col1],[col2],[col3],[part.Note],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[CurrentRecord])VALUES ( SOURCE.[PlantID] ,SOURCE.[PartID] ,SOURCE.[PartNo] ,SOURCE.[col1] ,SOURCE.[col2] ,SOURCE.[col3] ,SOURCE.[part.Note] ,SOURCE.[col4] ,SOURCE.[col5] ,SOURCE.[col6] ,SOURCE.[col7] ,SOURCE.[col8] ,SOURCE.[col9] ,SOURCE.[col10] ,SOURCE.[CurrentRecord]);</blockquote> 这篇关于在存储过程中将nvarchar值转换为数据类型int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！