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

Question

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.

You can also use Convert function if you want to convert the data with style. — Banketeshvar Narayan, Feb 09 '14 at 08:05

score 81 · Accepted Answer · answered Feb 09 '14 at 04:10

81

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

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

answered Feb 09 '14 at 04:10

Avi Turner

10,234
7
48
75

score 10 · Answer 2 · answered Jun 20 '17 at 14:37

10

Try Using

CONVERT(nvarchar(10),@ID)

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

answered Jun 20 '17 at 14:37

csk

179
1
7

1

That claim is unsubstantiated -- can go either way depending on your own data. In summing up this [answer](https://stackoverflow.com/a/707433/731081), `CONVERT` is SQL Server specific, `CAST` is ANSI. Recommended to use CAST where possible. – sonyisda1 Sep 26 '17 at 19:15

score 2 · Answer 3 · answered May 12 '18 at 06:09

2

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

answered May 12 '18 at 06:09

Seyed Hussein Mirzaki

321
3
7

How can i implement it in my code can you give me example – Abd Abughazaleh Aug 19 '20 at 13:47

score 2 · Answer 4 · answered Aug 19 '20 at 22:51

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

score 2 · Answer 5 · answered Oct 23 '20 at 19:26

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!

score 1 · Answer 6 · answered Jan 12 '21 at 10:28

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.

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

6 Answers6

Linked