CODEWITHSUNDEEP
sql
user3288804
user3288804

Reputation: 445

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.

Upvotes: 42

Views: 513902

Answers (6)

Yamo93
Yamo93

Reputation: 512

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.

Upvotes: 0

DigitalNomad
DigitalNomad

Reputation: 131

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!

Upvotes: 2

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

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

Upvotes: 3

Seyed Hussein Mirzaki
Seyed Hussein Mirzaki

Reputation: 361

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

Upvotes: 2

csk
csk

Reputation: 179

Try Using

CONVERT(nvarchar(10),@ID)

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

Upvotes: 10

Avi Turner
Avi Turner

Reputation: 10466

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

Upvotes: 87

Related Questions