[sql] Conversion failed when converting the nvarchar value ... to data type 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

Please help.

Try Using

CONVERT(nvarchar(10),@ID)

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

don't use string concatenation to produce sql, you can use sp_executesql system stored prcedure to execute sql statement with parameters

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

see sp_executesql

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

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:

SELECT * FROM RandomTable WHERE Id IN (SELECT Id FROM RandomTable WHERE [Number] = 13028533)

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:

SELECT * FROM RandomTable WHERE Id IN (SELECT Id FROM RandomTable WHERE [Number] = '13028533')

And it worked.

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:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'abcd' to data type int.

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!

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 :

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

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