EXECUTE sp_executesql not working in SQL Server

Question

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?

Answer 1

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