blocks|key|2968866|text|您正在尝试连接一个字符串和一个整数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2968867|您需要将@ID作为字符串进行cast。|offset|length|style|CODE|2968868|尝试：|2968869|SET+@sql=@sql%2B'+AND+Emp_Id_Pk='+%2B+CAST(@ID+AS+NVARCHAR(10))|code-block|syntax|javascript|2968870|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187928.aspx^0|0|4|3|E|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@$D|10|E|11|1|12]]|A|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|14|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

You are trying to concatenate a string and an integer. 
You need to <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx">cast</a> <code>@ID</code> as a string. 
try:

<pre><code>SET @sql=@sql+' AND Emp_Id_Pk=' + CAST(@ID AS NVARCHAR(10))
</code></pre>

blocks|key|165039|text|尝试使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|165040|CONVERT(nvarchar(10),@ID)|code-block|syntax|javascript|165041|这与cast相似，但成本更低(就时间消耗而言)|165042|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try Using

<pre><code>CONVERT(nvarchar(10),@ID)
</code></pre>

This is similar to cast but is less expensive(in terms of time consumed)

blocks|key|2968993|text|我在我的一个专栏中使用了一个关键字，我用brackets+[]解决了这个问题|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2968994|entityMap^0|K|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I was using a KEY word for one of my columns and I solved it with <code>brackets []</code>

blocks|key|2756350|text|我也遇到过同样的问题，我删除了有问题的专栏的约束，它对我起作用了。您可以检查文件夹约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2756351|捕获：|2756352|​|2756353|📷|atomic|offset|length|2756354|2756355|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/08bfb98948c75ceb6b97d5d185df8ea7.png|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@$I|Y|J|Z|1|10]]|A|$]]|$1|K|3|E|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

I have faced to the same problem, i deleted the constraint for the column in question and it worked for me. You can check the folder Constraints.
Capture :
<a href="https://i.stack.imgur.com/9Wccz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9Wccz.png" alt="enter image description here" /></a>

blocks|key|161251|text|不要使用字符串连接来生成sql，可以使用sp_executesql系统存储的程序来执行带参数的sql语句。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|161252|create+procedure+getdata+@ID+int,+@frm+varchar(250),+@to+varchar(250)+as
begin
++++declare+@sql+nvarchar(max),+@paramDefs+nvarchar(max);

++++set+nocount+on;

++++set+@sql+=+N'select+EmpName,+Address,+Salary+from+Emp_Tb+where+@id+is+null+or+Emp_Id_Pk+=+@id';
++++set+@paramDefs+=+N'@id+int';
++++execute+sp_executesql+@sql,+@paramDefs,+@id+=+@ID;
end|code-block|syntax|javascript|161253|请参阅sp_executesql|161254|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15^0|K|D|0|0|3|D|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

don't use string concatenation to produce sql, you can use <code>sp_executesql</code> system stored prcedure to execute sql statement with parameters
<pre><code>create procedure getdata @ID int, @frm varchar(250), @to varchar(250) as
begin
 declare @sql nvarchar(max), @paramDefs nvarchar(max);

 set nocount on;

 set @sql = N'select EmpName, Address, Salary from Emp_Tb where @id is null or Emp_Id_Pk = @id';
 set @paramDefs = N'@id int';
 execute sp_executesql @sql, @paramDefs, @id = @ID;
end
</code></pre>
see <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15" rel="nofollow noreferrer">sp_executesql</a>

blocks|key|162270|text|我使用最新版本的SSMS或sql+server+management+studio。我有一个SQL脚本(在查询编辑器中)，它有大约100行代码。这是我在查询中得到的错误：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|162271|Msg+245,+Level+16,+State+1,+Line+2
Conversion+failed+when+converting+the+nvarchar+value+'abcd'+to+data+type+int.|code-block|syntax|javascript|162272|Solution+-我以前见过这种错误，因为我忘记用单引号将一个数字(在varchar列中)括起来。|offset|length|style|BOLD|162273|顺便说一句，该错误消息具有误导性。查询编辑器中第70行的实际错误，而不是错误所说的第2行！|162274|entityMap^0|0|0|0|8|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I use the latest version of SSMS or sql server management studio. I have a SQL script (in query editor) which has about 100 lines of code. This is error I got in the query:
<pre><code>Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'abcd' to data type int.
</code></pre>
Solution - I had seen this kind of error before when I forgot to enclose a number (in varchar column) in single quotes.
As an aside, the error message is misleading. The actual error on line number 70 in the query editor and not line 2 as the error says!

blocks|key|2968932|text|出现此错误的原因是您试图将列DataType从String转换为int|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2968933|leagal+if+and+only+if|BOLD|2968934|2968935|表中没有在该列中包含字符串内容行|blockquote|2968936|2968937|2968938|所以只要确保之前插入的行的为compatible+with+the+new+changes即可|2968939|entityMap^0|E|8|N|6|W|3|0|0|L|0|L|0|0|0|0|0|6|7|E|V|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|H]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|I|3|-4|5|6|7|14|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|16|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|17|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|18|8|@$9|19|A|1A|B|H]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|R|$]]

You got this Error because you tried to convert column <code>DataType</code> from <code>String</code> to <code>int</code> which is

<code>leagal if and only if</code>

<blockquote>
 you dont have row in that table with string content inside that column 
</blockquote>

so just make sure your previously inserted Rows is <code>compatible with the new changes</code>

blocks|key|165082|text|当我使用where子句查看nvarchar字段但没有使用单引号时，我得到了这个错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|165083|我的无效SQL查询如下所示：|165084|SELECT+*+FROM+RandomTable+WHERE+Id+IN+(SELECT+Id+FROM+RandomTable+WHERE+[Number]+=+13028533)|code-block|syntax|javascript|165085|这不起作用，因为Number列的数据类型为nvarchar。这并不像我最初想的那样是一个int。|165086|我将其更改为：|165087|SELECT+*+FROM+RandomTable+WHERE+Id+IN+(SELECT+Id+FROM+RandomTable+WHERE+[Number]+=+'13028533')|165088|而且它起作用了。|165089|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|X|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I got this error when I used a where clause which looked at a nvarchar field but didn't use single quotes.
My invalid SQL query looked like this:
<pre><code>SELECT * FROM RandomTable WHERE Id IN (SELECT Id FROM RandomTable WHERE [Number] = 13028533)
</code></pre>
This didn't work since the Number column had the data type nvarchar. It wasn't an int as I first thought.
I changed it to:
<pre><code>SELECT * FROM RandomTable WHERE Id IN (SELECT Id FROM RandomTable WHERE [Number] = '13028533')
</code></pre>
And it worked.

I created the procedure listed below:

<pre><code>CREATE procedure getdata
(
 @ID int,
 @frm varchar(250),
 @to varchar(250)
)
AS
BEGIN

DECLARE @SQL nvarchar(500)


set @SQL = 'select'
set @SQL = @SQL + ' EmpName, Address, Salary from Emp_Tb where 1=1 '

IF (@ID &lt;&gt; '' and @ID is not null) 
 Begin 
 SET @sql=@sql+' AND Emp_Id_Pk=' +@ID 
 End 
END

print @sql
--execute (@sql)
</code></pre>

I try to execute it using:

<pre><code>**execute getdata 3,'','';**
</code></pre>

But I'm getting the following error:

<blockquote>
 Conversion failed when converting the nvarchar value 'select EmpName,
 Address, Salary from Emp_Tb where 1=1 AND Emp_Id_Pk=' to data type int
</blockquote>

Please help.

Conversion failed when converting the nvarchar value ... to data type int

我创建了下面列出的过程：CREATE procedure getdata(    @ID int,    @frm varchar(250),    @to varchar(250))ASBEGINDECLARE @SQL nvarchar(500)set @SQL = 'select'set @SQL = @SQL + ' EmpName, Address, Salary from Emp_Tb

问转换nvarchar值时转换失败...转换为数据类型int
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问转换nvarchar值时转换失败...转换为数据类型intEN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问转换nvarchar值时转换失败...转换为数据类型int
EN