当将nvarchar值…转换为数据类型int时，转换失败

I created the procedure listed below:

我创建了下面列出的程序:

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 <> '' and @ID is not null)     
  Begin     
   SET @sql=@sql+' AND Emp_Id_Pk=' +@ID   
  End 
END

print @sql
--execute (@sql)

I try to execute it using:

我试着用:

**execute getdata 3,'','';**

But I'm getting the following error:

但是我得到了如下的错误:

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

当将nvarchar值'select EmpName、Address、Salary from Emp_Tb(其中1=1,Emp_Id_Pk=')转换为数据类型int时，转换失败

Please help.

请帮助。

5 个解决方案

#1

You are trying to concatenate a string and an integer.
You need to cast @ID as a string.
try:

您正在尝试连接一个字符串和一个整数。您需要将@ID转换为字符串。试一试:

SET @sql=@sql+' AND Emp_Id_Pk=' + CAST(@ID AS NVARCHAR(10))

#2

Try Using

试着用

CONVERT(nvarchar(10),@ID)

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

这类似于cast，但是比较便宜(使用时间)

#3

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

我在其中一篇专栏中使用了一个关键词然后用括号[]解决了这个问题

#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: http://i.stack.imgur.com/9Wccz.png

捕捉:http://i.stack.imgur.com/9Wccz.png

#5

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

你得到这个错误是因为你试图将列数据类型从字符串转换为int类型

leagal if and only if

如果且仅当。

you dont have row in that table with string content inside that column

表中没有行，列中没有字符串内容

so just make sure your previously inserted Rows is compatible with the new changes

因此，只需确保先前插入的行与新的更改兼容即可

#1