0

Hi I need to run dynamic SQL Query. In that I need to add 'Sum' value to variable.

Here is my Query:

DECLARE @FinYear varchar(10), @TableName varchar(30), @Sql varchar(MAX)='',@FinStartDate datetime
    DECLARE @Op_P_loan numeric(9,2),@Op_R_loan numeric(9,2)`enter code here`
DECLARE CurQueryOp CURSOR FOR 
        SELECT FinDesc
        FROM FinancialYear
        WHERE FinStartDate <=@FromDate
        OPEN CurQueryOp
        FETCH NEXT FROM CurQueryOp INTO @FinYear
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @TableName = name
            FROM sys.tables
            WHERE name LIKE '%TransPass%'+REPLACE(@FinYear,'-','')+'%'
            IF ISNULL(@TableName,'') <> ''
            BEGIN
                SET @Sql='SELECT '+@Op_P_loan+'='+ISNULL(@Op_P_loan,0)+'+SUM(CASE WHEN Receipt = ''P'' THEN Amount ELSE 0 END), '+
                         ' '+@Op_R_loan+'='+ISNULL(@Op_R_loan,0)+'+SUM(CASE WHEN Receipt = ''R'' THEN Amount ELSE 0 END) '+
                         'FROM '+@TableName+' '+
                         'WHERE TranDate between ''2010/04/01'' AND '''+CONVERT(VARCHAR,DATEADD(DAY,-1,@FromDate),102)+''' '+
                         'AND LedgerCode=''300'' '+
                         'AND CustomerCode='''+@CustomerCode+''' '

                EXEC(@Sql)
            END
            FETCH NEXT FROM CurQueryOp INTO @FinYear
        END
        CLOSE CurQueryOp
        DEALLOCATE CurQueryOp

When I am executing this, I am getting the following error:

Arithmetic overflow error converting varchar to data type numeric.

Improve this question
3
  • Take a look at this answer on StackOverflow on the question Getting result of dynamic SQL into a variable for sql-server. Then, rewrite your query to just return the result in a variable (say @R), and maintain the running total in a separate variable (say @T). After retrieving the result from the query, add this to your running total (SET @T=@T+@R). Commented Oct 5, 2016 at 11:30
  • Also @Op_P_loan numeric(9,2) seems rather narrow for a numeric. Maximum 9 digits with 2 decimal places will overflow for results that are bigger than 9,999,999.99. Commented Oct 5, 2016 at 11:36
  • 1
    You should cast your non varchar values to varchar when concatinating. Also keep in mind that if you aren't checking your values for NULL any NULL value in there will wipe your whole result. Commented Oct 5, 2016 at 11:51

1 Answer 1

Reset to default
0

You are concatinating a NULL value whith @Op_P_loan and @Op_R_loan and the automatic conversion fail in this case. Try this: 

SET @Sql='SELECT @Op_P_loan='+ISNULL(@Op_P_loan,0)+'+SUM(CASE WHEN Receipt = ''P'' THEN Amount ELSE 0 END), '+
                     ' @Op_R_loan ='+ISNULL(@Op_R_loan,0)+'+SUM(CASE WHEN Receipt = ''R'' THEN Amount ELSE 0 END) '+
                     'FROM '+@TableName+' '+
                     'WHERE TranDate between ''2010/04/01'' AND '''+CONVERT(VARCHAR,DATEADD(DAY,-1,@FromDate),102)+''' '+
                     'AND LedgerCode=''300'' '+
                     'AND CustomerCode='''+@CustomerCode+''' '
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

About implicit conversion: msdn.microsoft.com/en-us/library/ms191530.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.