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  lt  gt     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

User · Answer

Try Using  CONVERT nvarchar 10   ID    This is similar to cast but is less expensive in terms of time consumed

User · Answer

You got this Error because you tried to convert column DataType  from String to int which is  leagal if and only if     you dont have row in that table with string content inside that column    so just make sure your previously inserted Rows is compatible with the new changes

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I have faced to the same problem  i deleted the constraint for the column in question and it worked for me  You can check the folder Constraints  Capture

User · Answer

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

User · Answer

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

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

Examples related to sql