A simple math procedure returning NULL?

Question

I would appreciate it if someone could tell me why this stored procedure returns NULL (I am using SQL Server 2005)? Thanks for the help.

CREATE PROCEDURE calc_runningtotal_averageprice_realisedpl @filled_size REAL, @reported_execution REAL, @old_running_total REAL, @old_average_price REAL, @new_running_total REAL OUTPUT, @new_average_price REAL OUTPUT, @realised_pl REAL OUTPUT AS BEGIN SET @new_running_total = @old_running_total + @filled_size SET @realised_pl = 0 IF SIGN(@filled_size) = SIGN(@old_running_total) BEGIN SET @new_average_price = (@filled_size * @reported_execution + @old_running_total * @old_average_price) / (@new_running_total) END ELSE BEGIN DECLARE @quantity REAL IF ABS(@reported_execution) < ABS(@old_running_total) SET @quantity = ABS(@reported_execution) ELSE SET @quantity = ABS(@old_running_total); SET @realised_pl = (@reported_execution - @old_average_price) * @quantity * SIGN(@filled_size) * -1; SET @new_average_price = CASE WHEN ABS(@filled_size) < ABS(@old_running_total) THEN @old_average_price WHEN ABS(@filled_size) = ABS(@old_running_total) THEN 0 WHEN ABS(@filled_size) > ABS(@old_running_total) THEN @reported_execution END END END 

IF I run the following, I get 3 NULLS

 DECLARE @new_running_total REAL DECLARE @new_average_price REAL DECLARE @realised_pl REAL EXEC calc_runningtotal_averageprice_realisedpl 1, 1, 2, 2, @new_running_total, @new_average_price, @realised_pl SELECT @new_running_total, @new_average_price, @realised_pl 

I expect something like 3, 1.66666, 0

Answer 1

You just don't decorate the output parameters in a call with OUTPUT :

 EXEC calc_runningtotal_averageprice_realisedpl 1, 1, 2, 2, @new_running_total OUTPUT, @new_average_price OUTPUT, @realised_pl OUTPUT