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.