none
Trouble using the Stored Value of a Sum(colname) in a Variable inside Stored Procedure

    Question

  • Not able to use the value that i store in variable @SumTotMem

    I have to use this in Where condition in my next query

    ALTER PROCEDURE [dbo].[PROCNAME] 
    @TEMPTABLE VARCHAR(10) = NULL,  
    as 
    DECLARE @SQLSTR AS NVARCHAR(MAX
    DECLARE @SumTotMem AS NUMERIC(5) 
     
    SELECT @SQLSTR = 'SET @SumTotMem = (SELECT SUM(TOT_MEM) FROM 
    '+QUOTENAME(@TEMPTABLE,"[")+
    WHERE COMBINE_CD = '+QUOTENAME(@TCombine_CD, '''')+')' 
     
    PRINT (@SQLSTR) 
    EXEC sp_executesql @SQLSTR, '@sumtotmem integer out', @sumtotmem out 

    Error : Arithmetic overflow error converting nvarchar to data type numeric.

    Question 2 : Is it possible to Fetch Sum using Dynamic Sql When using tablename that comes as parameter as above (@TEMPTABLE) like
     
    SELECT @SUMTOTMEM = SUM(TOT_MEM) FROM QUOTENAME(@TEMPTABLE, '[')  
    WHERE COMBINE_CD=QUOTENAME(@TEMPCOMBINE_CD, '''') 

    I get this Error " Invalid object name 'QUOTENAME' "

    any help appreciated.
    Friday, March 13, 2009 2:07 PM

Answers

  • Well...

    I have QUOTED_IDENTIFIER set OFF.
    It seems to work appropriately.

    I noticed that you used double quotes for the QUOTENAME() function - I didn't.


    DECLARE   
       @SQLStr        nvarchar(max),    
       @SumTotMem     numeric(5),  
       @TCombine_CD   varchar(20) = 'SomethingHere',  
       @TempTable     varchar(20) = '#YourTempTable';  
        
    SELECT @SQLStr = 'SET @SumTotMem = ( SELECT sum( Tot_Mem ) ' +  
                     'FROM ' + quotename( @TempTable, '[' ) + ' ' +  
                     'WHERE Combine_CD = ' + quotename( @TCombine_CD, '''' ) + ' )'    
     
    SELECT @SQLStr  
     
    SET @SumTotMem = ( SELECT sum( Tot_Mem ) FROM [#YourTempTable] WHERE Combine_CD = 'SomethingHere' ) 

    Sometimes, using good formating standards helps to see the issues easier.


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Saturday, March 14, 2009 4:27 PM
  •   I see no declaration for @TCombine_CD variable. You would get an overflow error when you are trying to convert the a numeric value to nvarchar. For example in your code, if @TCombine_CD was a numeric value and you were trying to convert it to NVARCHAR as the SQL String to be executed by sp_executesql has to be of NVARCHAR of type.

    The code below works fine.

    create table tbl (a int,b numeric(5,2))
    go
    insert
    into tbl values (1,5.2)
    go
    DECLARE
     @TEMPTABLE VARCHAR(40)   

    DECLARE @SQLSTR AS NVARCHAR(MAX)    
    DECLARE @CountVal AS NUMERIC(5)    
    DECLARE @value numeric(5,2)  
    set @value = 1.1  
    SET @TEMPTABLE = 'tbl' 
    SELECT @SQLSTR = N'select @CountVal=count(*) from '+QUOTENAME (@TEMPTABLE,'[')+ N' WHERE b > '+cast (@value as nvarchar(50))  
    PRINT (@SQLSTR)    
    EXEC sp_executesql @SQLSTR, N'@CountVal integer out', @CountVal out    
    select @CountVal 

    HTH

    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Saturday, March 14, 2009 5:09 PM
  • Thanx Arnie & Amit
    Amit - I had declared that variable just forgot to mention it here.
    Solution to the problem
    Error : Arithmetic overflow error converting nvarchar to data type numeric.
    was '+str(@SumTotMem)+' using the Str function.

    It was silly Mistake, I just used Str function
    & it Runs well.

    Thanks Guys for ur Support.

    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Monday, March 16, 2009 12:05 PM

All replies

  • It may be a problem with quoted identifier setting.

    Try setting 
    set quoted_identifier off

    and see if it works.

    /Kenneth
    Friday, March 13, 2009 2:59 PM
  • Thanks Kenneth

    i had already set the
    set Quoted_identifier off

    I guess the problem seems to be somthin else.
    Saturday, March 14, 2009 12:07 PM
  • Well...

    I have QUOTED_IDENTIFIER set OFF.
    It seems to work appropriately.

    I noticed that you used double quotes for the QUOTENAME() function - I didn't.


    DECLARE   
       @SQLStr        nvarchar(max),    
       @SumTotMem     numeric(5),  
       @TCombine_CD   varchar(20) = 'SomethingHere',  
       @TempTable     varchar(20) = '#YourTempTable';  
        
    SELECT @SQLStr = 'SET @SumTotMem = ( SELECT sum( Tot_Mem ) ' +  
                     'FROM ' + quotename( @TempTable, '[' ) + ' ' +  
                     'WHERE Combine_CD = ' + quotename( @TCombine_CD, '''' ) + ' )'    
     
    SELECT @SQLStr  
     
    SET @SumTotMem = ( SELECT sum( Tot_Mem ) FROM [#YourTempTable] WHERE Combine_CD = 'SomethingHere' ) 

    Sometimes, using good formating standards helps to see the issues easier.


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Saturday, March 14, 2009 4:27 PM
  •   I see no declaration for @TCombine_CD variable. You would get an overflow error when you are trying to convert the a numeric value to nvarchar. For example in your code, if @TCombine_CD was a numeric value and you were trying to convert it to NVARCHAR as the SQL String to be executed by sp_executesql has to be of NVARCHAR of type.

    The code below works fine.

    create table tbl (a int,b numeric(5,2))
    go
    insert
    into tbl values (1,5.2)
    go
    DECLARE
     @TEMPTABLE VARCHAR(40)   

    DECLARE @SQLSTR AS NVARCHAR(MAX)    
    DECLARE @CountVal AS NUMERIC(5)    
    DECLARE @value numeric(5,2)  
    set @value = 1.1  
    SET @TEMPTABLE = 'tbl' 
    SELECT @SQLSTR = N'select @CountVal=count(*) from '+QUOTENAME (@TEMPTABLE,'[')+ N' WHERE b > '+cast (@value as nvarchar(50))  
    PRINT (@SQLSTR)    
    EXEC sp_executesql @SQLSTR, N'@CountVal integer out', @CountVal out    
    select @CountVal 

    HTH

    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Saturday, March 14, 2009 5:09 PM
  • Thanx Arnie & Amit
    Amit - I had declared that variable just forgot to mention it here.
    Solution to the problem
    Error : Arithmetic overflow error converting nvarchar to data type numeric.
    was '+str(@SumTotMem)+' using the Str function.

    It was silly Mistake, I just used Str function
    & it Runs well.

    Thanks Guys for ur Support.

    • Marked as answer by Kinjal Gudhka Monday, March 16, 2009 12:10 PM
    Monday, March 16, 2009 12:05 PM