Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center > SQL Server Forums > SQL Server Express > Conversion failed when converting the varchar value 'null' to data type int. ERROR Please help!

Answered Conversion failed when converting the varchar value 'null' to data type int. ERROR Please help!

    •  
      Wednesday, January 19, 2011 3:59 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hi guys,

      I just want to run a query, but after awhile it gave me the Conversion failed error. Here's the query I am trying to run:

       

      select listing_id, rpt_id, name_en, phone
      from tsgb_extract_110107
      where rpt_id in (3300718,
      3305691,
      3234688,
      3358162,
      3423680,
      3406570,
      3368217,
      3363510,
      3311659,
      3456231,
      3355761,
      3404712,
      3284618,
      559992)

       

      It gave me results initially and then suddenly it gives me this error and all the results were gone.... I've tried to see if one of the fields has an null value, but listing_id, rpt_id, name_en,  and phone do not contain a null value.... It's so frustrating, please help.

       

      Al

       

    Answers

    • Wednesday, January 19, 2011 11:30 PM
      Moderator
       
       Answered Has Code
      Sign In to Vote
      0
      Sign In to Vote

      hi,

      I do believe your [rpt_id] column is not an integer based column but a varchar based one...

      this has implications as it could store values that can not be implicitely converted to an int datatype... so, if this is the case, you should not rely on your implicit conversion performed at run time in the IN predicate, as it assumes that, feeding the values list with integer as you do, the engine tries to implicetely convert for you the rpt_id value of each and every row to it's integer representation in order to compare it with the provided "list"... if the conversion fails for whatever reason an exception will be thrown...

      SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
	Id varchar(10) NULL
	);
GO
PRINT 'Implicit conversion';
INSERT INTO dbo.t VALUES ( 1 ), ( 2 ), ( 3 );
SELECT *
	FROM dbo.t WHERE Id IN ( 1, 3 );
GO
TRUNCATE TABLE dbo.t;
GO
PRINT 'still implicit conversion';
PRINT 'and a NULL value does not break it';
INSERT INTO dbo.t VALUES ( 1 ), ( 2 ), ( 3 ), ( NULL );
SELECT *
	FROM dbo.t WHERE Id IN ( 1, 3 );
GO
TRUNCATE TABLE dbo.t;
GO
PRINT 'still partial implicit conversion';
PRINT 'and a "NULL" string value, which is not a real NULL value, breaks it';
INSERT INTO dbo.t VALUES ( 1 ), ( 2 ), ( 3 ), ( 'null' );
SELECT *
	FROM dbo.t WHERE Id IN ( 1, 3 );
GO
TRUNCATE TABLE dbo.t;
GO
PRINT 'still partial implicit conversion';
PRINT 'and a string value, breaks it';
INSERT INTO dbo.t VALUES ( 1 ), ( 2 ), ( 'aa' ), ( 'null' );
SELECT *
	FROM dbo.t WHERE Id IN ( 1, 3 );
GO
TRUNCATE TABLE dbo.t;
GO
PRINT 'no conversion, it works as expected without exceptions';
INSERT INTO dbo.t VALUES ( '1' ), ( '2' ), ( 'aa' ), ( 'null' ), ( NULL );
SELECT *
	FROM dbo.t WHERE Id IN ( '1', '3' );
GO
DROP TABLE dbo.t;
--<-------
Implicit conversion
Id
----------
1
3

still implicit conversion
and a NULL value does not break it
Id
----------
1
3

still partial implicit conversion
and a "NULL" string value, which is not a real NULL value, breaks it
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'null' to data type int.
still partial implicit conversion
and a string value, breaks it
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'aa' to data type int.
no conversion, it works as expected without exceptions
Id
----------
1

      as you can see, no exception is thrown if no conversion is performed... back to your "null" conversion problem, it seems to me that you have a row where rpt_id is asctually set to a "null" string (and not a NULL value, which is very different), as in the case of the "aa" value, and so the engine can obviously not convert "null" into an integer, where it can for sure perform a real comparison of {NULL =# 1, 2, whatever...}, which yelds UNKNOWN and thus the result is False.. I presume that as usually the engine reports "NULL" (capitalized) and not "null" in it's messages... and it does not report "converting the varchar..."

      can you please check it?

      regards

      http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de