none
Error converting data type varchar to numeric.

    Question

  •  

    DECLARE @ENTITY nvarchar (100)

    set @ENTITY = 'AccidentDimension'

    DECLARE @FIELD nvarchar (100)

    set @FIELD = 'JurisdictionState'

    DECLARE @KEYID nvarchar (100)

    SET @KEYID = '1234567890'

    DECLARE @VALUE nvarchar (100)

    SET @VALUE = 'WI'

     

     

    DECLARE @WC_TABLE NVARCHAR(100)

    SET @WC_TABLE = 'WorkingCopyAdd' + @ENTITY

     

     

    DECLARE @SQL1 NVARCHAR (1000)

    SET @SQL1 = 'INSERT INTO ' + @WC_TABLE+ ' (Claim, '+ @Field +') VALUES ('''+ @KEYID +''', '''+@VALUE+''')'

     

    EXECUTE sp_executesql @SQL1

     

     

    Can somebody help me. I get this error:

    Error converting data type varchar to numeric.

     while executing this Dynamic TSQl Command

    Tuesday, September 25, 2007 3:35 PM

Answers

  • You are in wrong direction, The default won’t help you here..

    The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.

    In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…

    As per the BOL,

     

    Column definition

    No entry, no DEFAULT definition

    No entry, DEFAULT definition

    Enter a null value

    Allows null values

    NULL

    Default value

    NULL

    Disallows null values

    Error

    Default value

    Error

     

    So, you have to use the ISNULL function to fix your problem.

    Code Snippet

    Create table #Staging1

    (

                Id int,

                Name varchar(10)

    )

     

    Insert Into #Staging1 Values(1, NULL);

    Insert Into #Staging1 Values(1, 'test');

     

    Go

     

    Create table #Main

    (

                ID int NOT NULL,

                Name varchar(10) NOT NULL DEFAULT ('')

    );

     

    --Will Work Fine

    Insert Into #Main(ID)

                Select ID From #Staging1

     

    --Should Fail

    Insert Into #Main(ID,Name)

                Select ID,Name From #Staging1

     

    --Will Work

    Insert Into #Main(ID,Name)

                Select ID,Isnull(Name,'') From #Staging1

     

     

     

     

    Tuesday, September 25, 2007 6:20 PM

All replies

  • The statement you are executing is

     

    INSERT INTO WorkingCopyAddAccidentDimension (Claim, JurisdictionState) VALUES ('1234567890', 'WI')

     

    There is a bigger question than the varchar/numeric conversion, and that is why you are using dynamic SQL, but to answer the question you asked, it doesn't look like there should be a problem. Is this the exact code that is failing, or might it be failing on another @KEYID value?

     

    I suggest you look at  www.sommarskog.se/dynamic_sql.html for more on dynamic SQL, even thought it's tangential to your specific question here.

     

    Steve Kass

    Drew University

    http://www.stevekass.com

     

    Tuesday, September 25, 2007 3:51 PM
  •  

    Claim is of type: varchar

    JurisdictionState is char of length 2

     

    I seperated and inserted with each colunm at a time to know if its problem with Claim or JursidictionState. Well, both of the time I received same error.

    Tuesday, September 25, 2007 3:56 PM
  • The error may be caused by an incorrect default, as in teh following sample:

    CREATE TABLE a(i int, j INT DEFAULT('acs'))
    GO
    INSERT a(i)VALUES(1)

    also the error might be caused by a trigger. Do you have triggers on the table?
    Tuesday, September 25, 2007 4:08 PM
  •  

    Well I had DEFAULT '  '  (A Blank / Space)

     

    Is this the problem ?

    Tuesday, September 25, 2007 4:26 PM
  •  

    yes.. yes..

     

    Always try to stick with the column datatype while giving the default value..

     

    I am really wondering how SQL Server allows to create the default value with different datatype..

    Tuesday, September 25, 2007 4:41 PM
  •  

    CREATE TABLE WCAddAcciDim

    ([Claim] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' ',

    [JurisdictionState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' '

    )

    GO

    INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('12', 'IN')

    INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('1234567890', 'WI')

    select * from WCAddAcciDim

    DROP TABLE WCAddAcciDim

     

     

    Well I see no problem when I execute this.

     

    And when I execute this below line, just this line, without use  of Dynamic SQL

     

    INSERT dbo.WorkingCopyAddAccidentDimension(Claim, JurisdictionState) VALUES ('1234567890', 'WI')

     

    I get error.

     

    Very Strange.

    I created teh table WorkingCopyAddAccidentDimension same way as I did above, infact I copied those lines and rename the tabel name thats it.

     

     

    What might be the Hidden error, Any idea please

    Tuesday, September 25, 2007 4:57 PM
  • What is the error message you are getting… Verify the table schema using..

    Sp_help WorkingCopyAddAccidentDimensiona

     

    Tuesday, September 25, 2007 5:02 PM
  •  

    Mani:

     

    When I removed the Defaults  I was able to update and Insert as wanted and required. However i have NULLS in the rest of colunm. This table has  68 Colunms and I have multiple tables around 6of similar number of colunms. Now I am using the data from these Staging/ WorkingCopy tables and Inserting it back to main Table. Where certain colunm cannot be null. If there is a null in certain colunm it will not allow me to insert it. Thast why I chose to make BLANK as a default in WorkingCopy Table.

     

    Now that I have remove BLANK/ SPACE from teh default, is there any Standard way of Replacing these NULLS with the Blank /Space. Could you please suggest any way to do this?

     

    What does MS SQL Standards have to say on this?

     

    What I could think of is to replace each colunm with a space were ever there is NULL but I guess this is not the standard way of doing. Any suggestion or any modification on re-creating WorkingCopy Table with Defaults?

    Tuesday, September 25, 2007 5:59 PM
  • You are in wrong direction, The default won’t help you here..

    The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.

    In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…

    As per the BOL,

     

    Column definition

    No entry, no DEFAULT definition

    No entry, DEFAULT definition

    Enter a null value

    Allows null values

    NULL

    Default value

    NULL

    Disallows null values

    Error

    Default value

    Error

     

    So, you have to use the ISNULL function to fix your problem.

    Code Snippet

    Create table #Staging1

    (

                Id int,

                Name varchar(10)

    )

     

    Insert Into #Staging1 Values(1, NULL);

    Insert Into #Staging1 Values(1, 'test');

     

    Go

     

    Create table #Main

    (

                ID int NOT NULL,

                Name varchar(10) NOT NULL DEFAULT ('')

    );

     

    --Will Work Fine

    Insert Into #Main(ID)

                Select ID From #Staging1

     

    --Should Fail

    Insert Into #Main(ID,Name)

                Select ID,Name From #Staging1

     

    --Will Work

    Insert Into #Main(ID,Name)

                Select ID,Isnull(Name,'') From #Staging1

     

     

     

     

    Tuesday, September 25, 2007 6:20 PM
  •  

    Excellent

     

    Got it

     

    Thanks a lot Mani

    Tuesday, September 25, 2007 6:32 PM
  •  

    Running this on SQL Server 2005

     

     

    SELECT A.Columun1, B.Column1

    FROM Table1 A

    JOIN View1 B

    ON A.Column2 =  B.Column2

     

    Get the message

     

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

     

    Ifind it odd since both columns are type varchar. Then I try this

     

    SELECT A.Columun1, B.Column1

    FROM Table1 A

    JOIN View1 B

    ON CONVERT(VARCHAR(53),A.Column2) = CONVERT(VARCHAR(53), B.Column2)

     

    Still Get the message

     

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

     

     

    Column2 on both tables is varchar(53) . Contains alphanumeric text

     

    Thursday, August 07, 2008 6:22 PM
  • Would you be willing to supply the DDL for the table, the relevant components of the view and the relevant components of the tables that compose the view?

    Thursday, August 07, 2008 6:32 PM
    Moderator
  • I also incurred same error msg "Error converting data type nvarchar to numeric", solved by converting numeric to varchar datatype 

    Problem: TableA.EmpID = TableB.EmployeeID

    Here TableA.EmpID is varchar datatype and in other end TableB.EmployeeID was in Decimal datatype. Converted TableB.EmployeeID to varchar datatype. 

    Solution: TableA.EmpID = Cast (TableB.EmployeeID as Varchar)

    Shailesh
    Wednesday, July 07, 2010 12:01 PM
  • i know it might be an elementary check given all the expert advice provided in the thread, but i had a similar problem a few days ago with dynamic sql and i share your pain.

    make sure that you do have 3 single quotes around the actual values, but another thing i had issues with was the table name. try to cast is as varchar when you create the name.

    i.e. SET @WC_TABLE = 'WorkingCopyAdd' + cast(@ENTITY as varchar)


     

    Wednesday, July 07, 2010 12:28 PM