none
sp_executesql Error converting datatype nvarchar to numeric

    Question

  • I am getting error

    Error converting data type nvarchar to numeric on following script. Please help to advice, where is wrong.

    Declare

    @GTopTotalYieldnumeric (18,2)=0.0


    Declare

    @GBottomTotalYieldnumeric (18,2)=0.0


    Declare

    @GTotalYieldnumeric (18,2)=0.0


    Declare

    @CTopTotalYieldnumeric (18,2)=0.0


    Declare

    @CBottomTotalYieldnumeric (18,2)=0.0


    Declare

    @CTotalYieldnumeric (18,2)=0.0


    Declare

    @TotalTestInINT=0


    Declare

    @Testervarchar(10)='Total'


    Declare

    @Querynvarchar (Max)

     


    Set

    @query='INSERT INTO [database].dbo.table'


      

    +'([Testcell] '


      

    +',[Spindle] '


      

    +',[GTopYield] '


      

    +',[GBottomYield]'


      

    +' ,[GOverallYield]'


      

    +',[CTopYield]'


      

    +' ,[CBottomYield] '


      

    +',[COverallYield]'


      

    +',[DiskCount])'


    +' VALUES'


      

    +'('''',@Tester,@GTopTotalYield,@GBottomTotalYield,@GTotalYield,@CTopTotalYield,@CBottomTotalYield,@CTotalYield,@TotalTestIn)'


       

    Declare@Paramnvarchar (Max)=N'@Tester varchar(10),@GTopTotalYield numeric (18,2),@GBottomTotalYield numeric (18,2),@GTotalYield numeric (18,2),@CTopTotalYield numeric(18,2),@CBottomTotalYield numeric (18,2),@CTotalYield numeric (18,2),@TotalTestIn INT'


    EXECUTEsp_executesql@Query,@Param,@Tester,@GTopTotalYield,@GBottomTotalYield,@GTotalYield,@CTopTotalYield,CBottomTotalYield,@CTotalYield,@TotalTestIn

    Monday, September 09, 2013 3:10 AM

Answers

  • Sorry for bother you guys, I found my typo error

    EXECUTEsp_executesql@Query,@Param,@Tester,@GTopTotalYield,@GBottomTotalYield,

    @GTotalYield,@CTopTotalYield,CBottomTotalYield,@CTotalYield,@TotalTestIn

    should be @CBottomTotalYield instead of CBottomTotalYield.

    Thanks you all for your answers. :)

    Monday, September 09, 2013 6:55 AM

All replies

  • Please follow the way in this

    http://technet.microsoft.com/en-us/library/ms188001.aspx


    Many Thanks & Best Regards, Hua Min

    Monday, September 09, 2013 3:52 AM
  • Before executing the query, use PRINT command to see the framed query.

    Print @Query.

    Also the parameters/variables are added like string.

    It should be +'('''','+@Tester+',@'+GTopTotalYield+.... instead of +'('''',@Tester,@GTopTotalYield.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, September 09, 2013 4:04 AM
  • Hi,

    Why you need dynamic query here , you can insert directly :

    Declare
    @GTopTotalYield numeric (18,2)=0.0
    ,@GBottomTotalYield numeric (18,2)=0.0
    ,@GTotalYield numeric (18,2)=0.0
    ,@CTopTotalYield numeric (18,2)=0.0
    ,@CBottomTotalYield numeric (18,2)=0.0
    ,@CTotalYield numeric (18,2)=0.0
    ,@TotalTestIn INT=0
    ,@Tester varchar(10)='Total'
    ,@Query nvarchar (Max)
    DECLARE @table TABLE (Testcell VARCHAR(100),
    Spindle varchar(10),
    GTopYield numeric (18,2),
    GBottomYield numeric (18,2),
    GOverallYield numeric (18,2),
    CTopYield numeric (18,2),
    CBottomYield numeric (18,2),
    COverallYield numeric (18,2),
    DiskCount INT)
    INSERT @table 
    SELECT '',@Tester,@GTopTotalYield,@GBottomTotalYield,@GTotalYield,@CTopTotalYield,
    @CBottomTotalYield,@CTotalYield,@TotalTestIn
    SELECT * FROM @table


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 09, 2013 4:18 AM
    Moderator
  • Hi,

    Thanks for your advice, I am using dynamic query because

    Table name is dynamic and created by different script .

    My query is inserting to the table in the database.

    like this..

    Set @query='INSERT INTO [database].dbo.'+@tablename +''

    Can I achieve without using dynamic query ? Thanks in advance.

    Monday, September 09, 2013 5:33 AM
  • You need to use Dynamic sql for this.

    Many Thanks & Best Regards, Hua Min

    Monday, September 09, 2013 5:47 AM
  • Thanks for your reply , HuaMin Chan..

    I am looking at your link , but I still can't get where is wrong.if possible, please highlight me.

    Monday, September 09, 2013 6:22 AM
  • Please follow the syntax exactly like this

    DECLARE @InsertString NVARCHAR(500)
    DECLARE @OrderMonth INT
    
    -- Build the INSERT statement.
    SET @InsertString = 'INSERT INTO ' +
           /* Build the name of the table. */
           SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
           CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
           'Sales' +
           /* Build a VALUES clause. */
           ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
           ' @InsOrdMonth, @InsDelDate)'
    
    /* Set the value to use for the order month because
       functions are not allowed in the sp_executesql parameter
       list. */
    SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
    
    EXEC sp_executesql @InsertString,
         N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
           @InsOrdMonth INT, @InsDelDate DATETIME',
         @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
         @OrderMonth, @PrmDeliveryDate
    
    GO


    Many Thanks & Best Regards, Hua Min


    • Edited by HuaMin Chen Monday, September 09, 2013 6:46 AM
    Monday, September 09, 2013 6:45 AM
  • Sorry for bother you guys, I found my typo error

    EXECUTEsp_executesql@Query,@Param,@Tester,@GTopTotalYield,@GBottomTotalYield,

    @GTotalYield,@CTopTotalYield,CBottomTotalYield,@CTotalYield,@TotalTestIn

    should be @CBottomTotalYield instead of CBottomTotalYield.

    Thanks you all for your answers. :)

    Monday, September 09, 2013 6:55 AM