How to assign values for parameters in sql server dynamic query

Question

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.

Take a look at [this answer](http://stackoverflow.com/a/3840771/243373) on StackOverflow on the question [Getting result of dynamic SQL into a variable for sql-server](http://stackoverflow.com/q/3840730/243373). 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`). — TT., Oct 05 '16 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`. — TT., Oct 05 '16 at 11:36
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. — Jens, Oct 05 '16 at 11:51

score 0 · Answer 1 · answered Oct 05 '16 at 12:16

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+''' '

About implicit conversion: https://msdn.microsoft.com/en-us/library/ms191530.aspx — Marcos Alexandre Sedrez, Oct 05 '16 at 12:21

How to assign values for parameters in sql server dynamic query

1 Answers1