在存储过程中将nvarchar值转换为数据类型int时转换失败 [英] Conversion failed when converting the nvarchar value to datatype int in stored procedure

问题描述

大家好，

我已经打了好几天了。我正在使用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.5

I 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]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_RegionalPart]
	
@RegionID as int 
AS
BEGIN

Declare @sql as nvarchar(max)
Declare @sqltrunc as nvarchar(max)
Declare @dbase as nvarchar(100)

if @RegionID = 1 
	Select @dbase = 'USE NorthAmericanDW'
if @RegionID = 2
	Select @dbase = 'USE SouthAmericanDW'
if @RegionID = 3 
	Select @dbase = 'USE EuropeanDW'
if @RegionID = 4
	Select @dbase = 'USE AsianPacificDW'

If OBJECT_ID('tempdb.dbo.#tmp') is not null drop table tempdb.dbo.#tmp

Create 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,
				CurrentRecord

				FROM PartTABLE part
				INNER JOIN 1
				INNER JOIN 2
				INNER JOIN 3
				INNER JOIN 4
				INNER JOIN 5
				LEFT JOIN 1
				WHERE 1=1 
				AND join2.regionid =  ' + cast(@RegionID as nvarchar(1)) 

PRINT @sql

Insert Into #tmp 
exec sp_executesql  @sql

PRINT '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] OR
		TARGET.[Col1]  <> SOURCE.[Col1] OR
		TARGET.[Col2]  <> SOURCE.[Col2] OR
		TARGET.[Col3]  <> SOURCE.[Col3] OR
		TARGET.[PartNote] <> SOURCE.[PartNote],
		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]);'


PRINT  'INSERT STATEMENT: ' 
PRINT @SQL

exec sp_executesql  @sql


PRINT  'INSERT STATEMENT AFTER EXECUTION: ' 
PRINT @SQL


Drop Table #tmp

End

< br $> b $ b

我尝试了什么：



我试过：



CONVERT

CAST

ISNUMERIC

COLLATION



注 - >该特定字段具有来自不同语言的特殊字符拼音。我不确定为什么SQL无法识别类似于UTF-8格式的东西。我们在其他表中有很多字段，它们可以正常工作和显示数据。

What I have tried:

I have tried:

CONVERT
CAST
ISNUMERIC
COLLATION

NOTE --> 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 have

TARGET.[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时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！