Convert varchar string to decimal

Thanks

I have added these lines to my SP

DECLARE @sql nvarchar(512)
SET @sql = N'SELECT ' + @E
EXEC sp_executesql @query = @sql,@params = N'@V decimal(24,18) OUTPUT', @V = @V OUTPUT
Print @V

@E has been previously set to (0.093000000000+(0.75*0.004087939911))

This error is returned
Server: Msg 8115, Level 16, State 8, Line 101
Arithmetic overflow error converting numeric to data type numeric.

what is the output variable definition that you have defined for you sp?

Getting a little out of my depth here. @V is a local variable of type decimal(24,18) - not set to output or anything special. I just wanbt to get the result of this numeric string to use later in my SP (will be used in an UPDATE statement a little later to update a value

ok can you paste the entire SP here.

A bit difficult to do that as there is a bit of functionality theat is not obvious from the SP and that works fine. I'll describe it to the point where it is OK, and copy in the part that is a problem.

We have a table where one of the fields holds a 'mask' for formulae in this case it is (A+(0.75*B)) - there can be up to three variables in the mask.

The values of A and B are retrieved from another table based on parameters and put into the mask which creates a varchar string which is @E in my SP. @E is decaled as varchar(200). In the example I have been testing
@E = (0.093000000000+(0.75*0.004087939911)) - I can get to this point successfully
I have declared another variable @V as decimal(24,18) - a high level of precision as this is used in a financial system calculating returns.

I need @V to be the evaluated value of @E, which in this case is 0.096065948 and it must be as a decimal as it will be APPENDED or UPDATED into a table of other returns.

AS advised earlier, I have edited your code and added it into my SP after the @E is finalised as follows
DECLARE @sql nvarchar(512)
SET @sql = N'SELECT ' + @E
EXEC sp_executesql @query = @sql,@params = N'@V decimal(24,18) OUTPUT', @V = @V OUTPUT
Print @V

I'm using the print, only because I'm testing using Query analyzer. A more typical use after resolving the equation would be

INSERT INTO [Index Returns] ([INDEX],[DATE],BENCH_1M, BENCH_UNIT)
VALUES (@Idx, @IRDate,@V,@d2*(1+@V))

OK the logic is clear to me.the reason that I requested the entire SP is that it helps me understand it better. I am sure that the rest of the SP is fine since u  were able to work it till now. Also can you point me to the 101 line after the new code was inserted.

Will get back to you. Have just cut part of it out into new query in QA and it seems to work. Will try to quickly nut it out, otherwise will post it for you to assist

Thios is the 'short' version of what I was attempting

DECLARE @E      varchar(200),
      @V      decimal(24,18),
      @sql      nvarchar(512)

SET @E = (0.093000000000+(0.75*0.004087939911))
PRINT @E
SET @sql = N'SELECT ' + @E
PRINT @sql
EXEC sp_executesql @query = @sql,@params = N'@V decimal(24,18) OUTPUT', @V = @V OUTPUT
print @V

When run in QA I get the following messages

0.09606595493325
SELECT 0.09606595493325

(1 row(s) affected)

Note @V did not return (presumably as it was an output variable in the EXEC statement. The correct value did show in the grid. I am most intrigued that @E was evaluated and that was printed. That is largely what I need. Next message will have my Original SP

This is orginal SP to the point of the problem

DECLARE       @IRIndex      varchar(70),
                  @IRDate            datetime,
                  @iCount            int,
                  @Idx            varchar(70),
                  @A            varchar(70),
                  @B            varchar(70),
                  @C            varchar(70),
                  @E            varchar(200),
                  @V            decimal(38,30),
                  @sql            nvarchar(512)

      SET @IRIndex = 'MSCI Emerging Markets Index (NZD)'
      print @IRIndex
      SET @IRDate = CONVERT(DATETIME, '2005-09-30 00:00:00', 102)
      print @IRDate
      SELECT @iCount = (SELECT COUNT([INDEX]) FROM [Index Combinations] WHERE [SOURCE INDEX A] = @IRIndex OR [SOURCE INDEX B] = @IRIndex OR [SOURCE INDEX C] = @IRIndex)
      print @icount

      IF @iCount = 0 GOTO ENDPROC

      DECLARE curIRC CURSOR LOCAL FAST_FORWARD
      FOR
      SELECT [INDEX], [SOURCE INDEX A],[SOURCE INDEX B],[SOURCE INDEX C],[EQUATION]
      FROM [Index Combinations]
      WHERE  [SOURCE INDEX A] = @IRIndex OR [SOURCE INDEX B] = @IRIndex OR [SOURCE INDEX C] = @IRIndex

      OPEN curIRC

      FETCH NEXT FROM curIRC
      INTO @Idx, @A, @B, @C, @E
      print @Idx
      print @A
      print @B
      print @C
      print @E

      WHILE @@FETCH_STATUS = 0
      BEGIN
            DECLARE @i1 int, @i2 int, @i3 int
            SET @i1 = (SELECT COUNT([INDEX]) FROM [Index Returns] WHERE [INDEX] = @A AND [DATE] = @IRDate)
            SET @i2 = (SELECT COUNT([INDEX]) FROM [Index Returns] WHERE [INDEX] = @B AND [DATE] = @IRDate)
            SET @i3 = (SELECT COUNT([INDEX]) FROM [Index Returns] WHERE [INDEX] = @C AND [DATE] = @IRDate)
            print @i1
            print @i2
            print @i3
            IF @i1 = 0 OR @i2 = 0 OR ( @C is not null and @i3 = 0)    -- This Index Return not yet available
                  BEGIN
                        GOTO EndEquation
                  END
            
            DECLARE @d1 decimal(12,12), @d2 decimal(12,12), @d3 decimal(12,6)
            SET @d1 = (SELECT BENCH_1M FROM [Index Returns] WHERE [INDEX] = @A AND [DATE] = @IRDate)
            print @d1
            SET @d2 = (SELECT BENCH_1M FROM [Index Returns] WHERE [INDEX] = @B AND [DATE] = @IRDate)
            print @d2
            SET @d3 = (SELECT BENCH_1M FROM [Index Returns] WHERE [INDEX] = @C AND [DATE] = @IRDate)
            print @d3
            IF @d1 IS NULL OR @d2 IS NULL OR (@C is not null and @d3 IS NULL) -- NULL value for Return
                  BEGIN
                        GOTO EndEquation
                  END

            --Replace "A" in equation with value
            print @E
            SET @E = REPLACE(@E,'A',@d1)
            print @E
            --Replace "B" in equation with value
            SET @E = REPLACE(@E,'B',@d2)
            print @E
            --Replace "C" in equation with value
            IF @i3 <> 0
            BEGIN
                  SET @E = REPLACE(@E,'C',@d3)
            END

            --Check for previous month data for this Index Combination
            SET @i1 = (SELECT COUNT([INDEX]) FROM [Index Returns] WHERE [INDEX] = @Idx AND [DATE] = dbo.DateAdder(@IRDate,-1))
            IF @i1 = 0
                  BEGIN
                        INSERT INTO [Index Returns] ([INDEX],[DATE],BENCH_UNIT)
                        VALUES (@Idx, dbo.DateAdder(@IRDate,-1),1)
                  END

            --Calculate the equation
            print @E
            SET @sql = N'SELECT ' + @E
            EXEC sp_executesql @query = @sql,@params = N'@V decimal(24,18) OUTPUT', @V = @V OUTPUT
            --SET @V = CONVERT(decimal(38,24),RTRIM(LTRIM(@E)))
            print @V

Now I find that this is working and returning the correct value to the grid. My error is coming from further down where I haven't looked yet. My problem is to grab that output value into the local variable @V so I can use it further on.

well you have already grabbed the value.. @V is the result and you may use it as any other variable that you have.

your variable v is defined as @V  decimal(38,30) at the start of the SP.. ther arithmetic overflow error can ocur if you are assigning any operation done on this number to a lower precision,scale variable. so you may like to go through your code to see if that is happening. And stick to the same definition for all numeric variables to reduce chances of overflows happening.

Not quite

If you look at the print statements from this simplified routine

DECLARE @E      varchar(200),
      @V      decimal(24,18),
      @sql      nvarchar(512)

SET @E = (0.093000000000+(0.75*0.004087939911))
SET @sql = N'SELECT ' + @E
EXEC sp_executesql @query = @sql,@params = N'@V decimal(24,18) OUTPUT', @V = @V OUTPUT
print 'V = '
print @V

The messages are

(1 row(s) affected)

V =

There is no printed value for @V. The value is in the grid, but how do I grab it for further use. @V appears to be NULL

Just change the datatype in the dynamic SQL.


DECLARE @result DECIMAL(24, 18), @sql NVARCHAR(512)
 
SET @sql = N'SELECT ' + @E
 
EXEC sp_executeSql @sql, N'@result DECIMAL(24, 18) OUTPUT', @result OUTPUT

PRINT @result