转换nvarchar值时转换失败 [英] Conversion failed when converting the nvarchar value

问题描述

我有这个动态查询，每次通过while循环时都会创建一个新表。但我得到了这个错误，我不知道为什么它会犯错误，今天大部分都被困在这个...

据我所知，我不是确定转换错误发生的位置。

这是我的代码;

 DECLARE @Count INT =（SELECT COUNT（*） ）来自PayComponents） - 这是我要循环的次数。 61 
 DECLARE @Pcomp INT ='1' - 开始于1 
 DECLARE @Table VARCHAR（10）='PComp' - 表名的一部分
 DECLARE @TableName VARCHAR（10 ）--declare保存全表名
 
 WHILE @Pcomp< = @Count  - 而1小于或=到61循环
 BEGIN 
 DECLARE @str VARCHAR （MAX）
 SET @TableName = CONCAT（@ Table，@ Pcomp） - 全表名... Pcomp1，Pcomp2，Pcomp3，Pcomp4等
 
 PRINT @TableName 
 SET @str ='
 IF OBJECT_ID（''dbo。'+ QUOTENAME（@TableName）+'''，''U''）IS NOT NULL 
 Drop Table'+ QUOTENAME（@TableName） +'
 
 CREATE TABLE'+ QUOTENAME（@TableName）+'
（
 EeID INT，
 EeRef VARCHAR（10），
 Name VARCHAR（ 50），
 PayCompID SMALLINT，
 PayCompDesc VARCHAR（50），
 RateLastMonth MONEY，
 RateThisMonth MONEY 
）
 
 INSERT INTO'+ QUOTENAME（@TableName）+'（EeID，EeRef，Name，PayCompID，PayCompDesc，RateLastMonth）
 SELECT 
 ed.EeID，
 ed.EeR ef，
 CONCAT（Forename，''''，Surname）AS''Name''，
 eec.PCompID，
 eec.PCDescr，
 eec.Rate 
来自EeDetails ed 
 INNER JOIN UnityEeComponents eec ON eec.EeRef = ed.EeRef 
 CROSS JOIN PayrollRuns pr 
 WHERE eec.PCompID ='+ QUOTENAME（@Pcomp）+'AND pr.RunID =（SELECT RunID FROM PayrollRuns WHERE RunDate ='''2019-04-30''）
 ORDER BY ed.EeID 
 
 UPDATE'+ QUOTENAME（@TableName）+'
 SET 
'+ QUOTENAME（@TableName）+'。RateThisMonth = EeComponents.ECRate 
 FROM'+ QUOTENAME（@TableName）+'
 INNER JOIN 
 EeComponents 
 ON 
'+ QUOTENAME（@TableName）+'。EeID = EeComponents.EeID 
 WHERE EeComponents.PCompID ='+ @Pcomp +'AND EeComponents.RunID =（SELECT RunID FROM PayrollRuns WHERE RunDate =''2019 -02-08''）;'
 
 EXEC SP_EXECUTESQL @str 
 SET @Pcomp = CONVERT（VARCHAR（10），@ Pcomp）+ 1; 
 END; 

任何帮助都将不胜感激！谢谢

解决方案

以下是问题：

' + QUOTENAME（@TableName）+'。EeID = EeComponents.EeID 
 WHERE EeComponents.PCompID ='+ @Pcomp +'AND EeComponents.RunID =（SELECT RunID FROM PayrollRuns WHERE RunDate ='''2019-02-08' '）;'
 

@Pcomp是INT，需要进行类型转换为VARCHAR

 + QUOTENAME（@TableName）+ 
'.EeID = EeComponents.EeID 
 WHERE EeComponents.PCompID ='+ cast（@Pcomp AS varchar（10） ））+'和EeComponents.RunID =（SELECT RunID FROM PayrollRuns WHERE RunDate ='''2019-02-08''）;'

I have this dynamic query that creates a new table each time it goes through the while loop. But i am getting that error and im not sure why its trowing an error, been stuck on this for most of today...

As far as i can see im not sure where the conversion error is happening.

Here is my code;

DECLARE @Count INT = (SELECT COUNT(*) FROM PayComponents) --This is how many times im going to loop. 61
DECLARE @Pcomp INT = '1' --starting at 1
DECLARE @Table VARCHAR(10) = 'PComp' --Part of the table name
DECLARE @TableName VARCHAR(10) --declare to hold full table name

WHILE @Pcomp <= @Count --while 1 is less than or = to 61 loop
BEGIN
DECLARE @str VARCHAR(MAX)
SET @TableName = CONCAT(@Table, @Pcomp) --Full table name... Pcomp1, Pcomp2, Pcomp3, Pcomp4 etc

PRINT @TableName
SET @str = '
IF OBJECT_ID(''dbo.' + QUOTENAME(@TableName) + ''', ''U'') IS NOT NULL
Drop Table ' + QUOTENAME(@TableName) + '

CREATE TABLE ' + QUOTENAME(@TableName) + '
(
EeID INT,
EeRef VARCHAR(10),
Name VARCHAR(50),
PayCompID SMALLINT,
PayCompDesc VARCHAR(50),
RateLastMonth MONEY,
RateThisMonth MONEY
)

INSERT INTO ' + QUOTENAME(@TableName) + ' (EeID, EeRef, Name, PayCompID, PayCompDesc, RateLastMonth)
SELECT
ed.EeID,
ed.EeRef,
CONCAT(Forename, '' '', Surname) AS ''Name'',
eec.PCompID,
eec.PCDescr,
eec.Rate
FROM EeDetails ed
INNER JOIN UnityEeComponents eec ON eec.EeRef = ed.EeRef
CROSS JOIN PayrollRuns pr
WHERE eec.PCompID = ' + QUOTENAME(@Pcomp) + ' AND pr.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019-04-30'')
ORDER BY ed.EeID

UPDATE ' + QUOTENAME(@TableName) + '
SET 
' + QUOTENAME(@TableName) + '.RateThisMonth = EeComponents.ECRate
FROM ' + QUOTENAME(@TableName) + '
INNER JOIN 
EeComponents
ON 
' + QUOTENAME(@TableName) +'.EeID = EeComponents.EeID
WHERE EeComponents.PCompID = ' + @Pcomp + ' AND EeComponents.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019-02-08'');'

EXEC SP_EXECUTESQL @str
SET @Pcomp = CONVERT(VARCHAR(10), @Pcomp) + 1;
END;

Any help would be greatly appreciated! Thanks

解决方案

Here is the issue:

' + QUOTENAME(@TableName) +'.EeID = EeComponents.EeID
WHERE EeComponents.PCompID = ' + @Pcomp + ' AND EeComponents.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019-02-08'');'

@Pcomp is INT, this needs to be type-casted to VARCHAR

 + QUOTENAME(@TableName) + 
		'.EeID = EeComponents.EeID
WHERE EeComponents.PCompID = ' + cast(@Pcomp AS varchar(10)) + ' AND EeComponents.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019-02-08'');'

这篇关于转换nvarchar值时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！