0

I'm trying to implement a mechanism that would perform calculations based on dynamically defined algorithms. The way I do it is:

  1. Build a string containing the definition of all the variables along with their values,
  2. Fetch (from a table) the calculation formula,
  3. Invoke sp_executesql.

Here is the contents of the string passed as the calculation (contents of variable @_l_Execute_Formula):

DECLARE @_1 FLOAT = 678;
DECLARE @_2 FLOAT = NULL;
DECLARE @_3 FLOAT = NULL;
SET @_l_Result = @_1 + @_2 + @_3

and the invocation is:

EXECUTE sp_executesql   @_l_Execute_Formula            , 
                      N'@_l_Result FLOAT OUTPUT'       , 
                        @_l_Result = @_l_Result OUTPUT   ;

I receive no error message but @_l_Result is NULL.

What am I doing wrong?

halfer
  • 19,824
  • 17
  • 99
  • 186
FDavidov
  • 3,505
  • 6
  • 23
  • 59
  • I would expect the value to be *`NULL`*. `[Some value] + NULL = NULL`. Be it a `float` (don't use `float`, it's an awful data type), a `varchar`, or anything else. *`NULL`* is an unknown value; adding it to any other known values causes the resulting value to be unknown as well (thus *`NULL`*). – Thom A May 10 '18 at 09:56

1 Answers1

2

The sum of float values with NULL return NULL. You might want to escape NULL values.

DECLARE @_l_Execute_Formula NVARCHAR(MAX) = '

DECLARE @_1 FLOAT = 678;
DECLARE @_2 FLOAT = NULL;
DECLARE @_3 FLOAT = NULL;
SET @_l_Result = ISNULL(@_1, 0) + ISNULL(@_2, 0) + ISNULL(@_3, 0)'

DECLARE @_l_Result FLOAT

EXECUTE sp_executesql   @_l_Execute_Formula            , 
                      N'@_l_Result FLOAT OUTPUT'       , 
                        @_l_Result = @_l_Result OUTPUT;


SELECT @_l_Result  -- Result 678
EzLo
  • 13,780
  • 10
  • 33
  • 38