none
Using a variable to execute a SQL statement - Returns error Arithmetic overflow error converting varchar to data type numeric RRS feed

  • Question

  • How do I get around the error "Arithmetic overflow error converting varchar to data type numeric" ? I get the error when I execute my stored procedure.

    I have in my insert block of the spoc columns that are numeric. (Total, Approved, Rejected, InProcess)

    Here is the code snippet:

           SET @sql = 'INSERT INTO ['+@DatabaseTable+']' + '
             ([ClientCode]
             ,[SortOrder]
             ,[CountryCode]
             ,[Country]
             ,[Total]
             ,[Approved]
             ,[Rejected]
             ,[InProcess]
             ,[LastUpdateID]
             ,[LastUpdateDate]
             ,[LastUpdateTime])
            VALUES
             (' +
             @ClientCode + ', ' +
             @SortOrder + ', ' +
             @CountryCode + ', ' +
             @Country   + ', ' +
             @Total + ', ' +
             @Approved + ', ' +
             @Rejected + ', ' +
             @InProcess + ', ' +
             @LastUpdateID + ', ' +
             @LastUpdateDate + ', ' +
             @LastUpdateTime + ', ' +
            ')'

    Thursday, July 21, 2011 2:54 PM

Answers

  • --- use the below code and let us know the results
        SELECT @sql = 'INSERT INTO ['+@DatabaseTable+']' + '
         ([ClientCode]
         ,[SortOrder]
         ,[CountryCode]
         ,[Country]
         ,[Total]
         ,[Approved]
         ,[Rejected]
         ,[InProcess]
         ,[LastUpdateID]
         ,[LastUpdateDate]
         ,[LastUpdateTime])
         VALUES
         (' +
         @ClientCode + ', ' +
         convert(decimal(5,2),@SortOrder) + ', ' +
         @CountryCode + ', ' + 
         @Country  + ', ' +
         convert(decimal(5,8),@Total) + ', ' +
         convert(decimal(5,8),@Approved) + ', ' +
         convert(decimal(5,8),@Rejected) + ', ' +
         convert(decimal(5,8),@InProcess) + ', ' +
         @LastUpdateID + ', ' +
         convert(decimal(5,8),@LastUpdateDate) + ', ' +
         convert(decimal(5,6),@LastUpdateTime) + ', ' +
         ')'
    
    
    

    • Marked as answer by mh53j_fe Thursday, July 21, 2011 8:36 PM
    Thursday, July 21, 2011 3:16 PM

All replies

  • can you also give us your table definition, (sp_help <your_table> )

     

    Thursday, July 21, 2011 3:01 PM
  • Here you go. Thank you for your help.

     

    Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
    ClientCode char no 20             no no no SQL_Latin1_General_CP1_CI_AS
    SortOrder decimal no 5 2 0 no (n/a) (n/a) NULL
    CountryCode char no 2             no no no SQL_Latin1_General_CP1_CI_AS
    Country varchar no 250             yes no yes SQL_Latin1_General_CP1_CI_AS
    Total decimal no 5 8 0 yes (n/a) (n/a) NULL
    Approved decimal no 5 8 0 yes (n/a) (n/a) NULL
    Rejected decimal no 5 8 0 yes (n/a) (n/a) NULL
    InProcess decimal no 5 8 0 yes (n/a) (n/a) NULL
    LastUpdateID char no 50             yes no yes SQL_Latin1_General_CP1_CI_AS
    LastUpdateDate decimal no 5 8 0 yes (n/a) (n/a) NULL
    LastUpdateTime decimal no 5 6 0 yes (n/a) (n/a) NULL
    Thursday, July 21, 2011 3:04 PM
  • --- use the below code and let us know the results
        SELECT @sql = 'INSERT INTO ['+@DatabaseTable+']' + '
         ([ClientCode]
         ,[SortOrder]
         ,[CountryCode]
         ,[Country]
         ,[Total]
         ,[Approved]
         ,[Rejected]
         ,[InProcess]
         ,[LastUpdateID]
         ,[LastUpdateDate]
         ,[LastUpdateTime])
         VALUES
         (' +
         @ClientCode + ', ' +
         convert(decimal(5,2),@SortOrder) + ', ' +
         @CountryCode + ', ' + 
         @Country  + ', ' +
         convert(decimal(5,8),@Total) + ', ' +
         convert(decimal(5,8),@Approved) + ', ' +
         convert(decimal(5,8),@Rejected) + ', ' +
         convert(decimal(5,8),@InProcess) + ', ' +
         @LastUpdateID + ', ' +
         convert(decimal(5,8),@LastUpdateDate) + ', ' +
         convert(decimal(5,6),@LastUpdateTime) + ', ' +
         ')'
    
    
    

    • Marked as answer by mh53j_fe Thursday, July 21, 2011 8:36 PM
    Thursday, July 21, 2011 3:16 PM
  • You get around the problem by stop inlining variables.

           SET @sql = 'INSERT INTO ' + quotename(@DatabaseTable) + '
             ([ClientCode]
             ,[SortOrder]
             ,[CountryCode]
             ,[Country]
             ,[Total]
             ,[Approved]
             ,[Rejected]
             ,[InProcess]
             ,[LastUpdateID]
             ,[LastUpdateDate]
             ,[LastUpdateTime])
            VALUES
             (@ClientCode,
             @SortOrder,
             @CountryCode,
             @Country,
             @Total,
             @Approved,
             @Rejected,
             @InProcess,
             @LastUpdateID,
             @LastUpdateDate,
             @LastUpdateTime,)'
    
    EXEC sp_executesql @sql,
         N'@ClientCode char(3),
           @SortOrder int,
           ...',
           @ClientCode, @SortOrder, ....

    Clean and easy, isn't it?

    Of course, a better question is: why is the table name a variable at all?

    In any case, on my web site you will find an article about dynamic SQL, which I recommend that you read. You will learn more about sp_executesql, and you will also learn when you should use dynamic SQL - and when you should not: http://www.sommarskog.se/dynamic_sql.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 21, 2011 9:41 PM