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.

1

6 Answers

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))

0

Try Using

CONVERT(nvarchar(10),@ID)

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

1

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

1

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 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 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.