T-SQL - Loss of float precision when using procedures

Question

I have been trying to create a T-SQL procedure where I sum all of the family's monthly income into a variable @prihodki and at the same time, I sum all of the family's monthly expense into a variable @odhodki. Printing a variable inside this procedure outputs the correct answer, however when I try to execute the procedure and store the returned value into a variable, the precision gets lost.

CREATE PROCEDURE izracunajPromet_TaMesec
AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @prihodki float, @odhodki float;

        SELECT @prihodki = SUM(t.Vrednost) 
        FROM Transakcija t 
        WHERE YEAR(t.Datum) = YEAR(current_timestamp) 
          AND MONTH(t.Datum) = MONTH(current_timestamp) 
          AND t.ID_Družine = 1 
          AND t.Vrednost >= 0;

        SELECT @odhodki = SUM(t.Vrednost) 
        FROM Transakcija t 
        WHERE YEAR(t.Datum) = YEAR(current_timestamp) 
          AND MONTH(t.Datum) = MONTH(current_timestamp) 
          AND t.ID_Družine = 1 
          AND t.Vrednost <= 0;

        IF (@prihodki IS NOT NULL AND @odhodki IS NOT NULL)
            RETURN CAST((@prihodki + @odhodki) AS NUMERIC(10, 2));
        ELSE IF (@prihodki IS NOT NULL AND @odhodki IS NULL)
        BEGIN
            PRINT @prihodki;
            RETURN CAST(@prihodki AS float);
        END
        ELSE 
            RETURN CAST(@odhodki AS NUMERIC(10, 2));
    END

    RETURN 0;
go

DECLARE @rezultat float;

EXEC @rezultat = izracunajPromet_TaMesec;

PRINT @rezultat;

The procedure prints out a value 67.73, while the execution and storage method returns a value of 67.

I have already tried casting and converting, but none of these solutions worked.

NOTE: Focus on ELSE IF part, because that's where I have been getting this precision loss and I assume that once this is solved, I will be able to update IF and ELSE parts too.

Answer 1

As explained in Returning Data Using a Return Code:

A procedure can return an integer value called a return code to indicate the execution status of a procedure.

Review the rest of the document (Return Data from a Stored Procedure) for ways to return non-integer data.

---

As for improving the code, there are several opportunities. It seems more natural to make this a user-defined function, but I have kept the stored procedure and added an output parameter for the result. It isn't clear why the values are being summed separately based on their signs and then added together when a simple sum would produce the same result. (The only benefit is an errant print statement if there are no non-positive values.) Note that the where condition has been made sargable so that an index can be used to improve performance.

create procedure izracunajPromet_TaMesec
  @Result as Numeric(10,2) output
  as
  begin

  set nocount on

  declare @prihodki as Numeric(10,2), @odhodki as Numeric(10,2);

  -- Get the current date/time.
  declare @Now as DateTime = Current_Timestamp;
  -- Calculate the first date of the current month.
  declare @MonthStart as Date = DateAdd( day, 1 - Day( @Now ), @Now );
  -- Calculate the first date of the next month.
  declare @NextMonthStart as Date = DateAdd( month, 1, @MonthStart );

  select
    -- Use conditional aggregation to get both sums from a single pass through the data.
    @prihodki = sum( case when Vrednost >= 0 then Vrednost end ),
    @odhodki = sum( case when Vrednost <= 0 then Vrednost end ) 
    from Transakcija
    where
      -- Filter the date(time) using a half-open interval so that an index can be used.
      @MonthStart <= Datum and Datum < @NextMonthStart and
      ID_Družine = 1;

    -- If either value is   NULL   then substitute   0.0   and return the result.
    set @Result = Coalesce( @prihodki, 0.0 ) + Coalesce( @odhodki, 0.0 );

    return;

  end;

Sample usage:

declare @MyResult as Numeric(10,2);
execute izracunajPromet_TaMesec @Result = @MyResult output;
select @MyResult;

Answer 2

After struggling for a while, I finally came up with an idea to return the value in form of a table and extract the value from it. This may or may not be the perfect solution, but it works. I am open to optimizations.

CREATE PROCEDURE izracunajPromet_TaMesec
AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @result TABLE (Rezultat float);
        DECLARE @prihodki float, @odhodki float;

        SELECT @prihodki = SUM(t.Vrednost) 
        FROM Transakcija t 
        WHERE YEAR(t.Datum) = YEAR(current_timestamp) 
          AND MONTH(t.Datum) = MONTH(current_timestamp) 
          AND t.ID_Družine = 1 
          AND t.Vrednost >= 0;

        SELECT @odhodki = SUM(t.Vrednost) 
        FROM Transakcija t 
        WHERE YEAR(t.Datum) = YEAR(current_timestamp) 
          AND MONTH(t.Datum) = MONTH(current_timestamp) 
          AND t.ID_Družine = 1 
          AND t.Vrednost <= 0;

        IF @prihodki IS NULL
            SET @prihodki = 0;
        IF @odhodki IS NULL
            SET @odhodki = 0;

        INSERT INTO @result VALUES (@prihodki + @odhodki);
        SELECT * FROM @result;
    END

    RETURN 0;
go

DECLARE @result TABLE (Rezultat float);
DECLARE @numres float;
INSERT @result exec izracunajPromet_TaMesec;
SELECT @numres = Rezultat FROM @result;
PRINT @numres;