Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

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.

like image 705
user3288804 Avatar asked Feb 09 '14 04:02

user3288804


People also ask

How convert NVARCHAR value to datatype int in SQL Server?

1 Answer. "CONVERT" considers the column name, not the string that has the column name; your current expression is attempting to convert the string A. my_NvarcharColumn to an integer instead of the column content. SELECT convert (int, N'A.

What is a NVARCHAR value?

nvarchar [ ( n | max ) ] Variable-size string data. n defines the string size in byte-pairs and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^30-1 characters (2 GB). The storage size is two times n bytes + 2 bytes.

What is data type NVARCHAR?

The NVARCHAR data type stores character data in a variable-length field. Data can be a string of single-byte or multibyte letters, digits, and other characters that are supported by the code set of your database locale.


1 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)) 
like image 108
Avi Turner Avatar answered Sep 20 '22 14:09

Avi Turner