Scalar-valued function returning NULL-CodePudding

I have the below function, and for the life of me, I cannot get it to return a value, I get NULL every time.

I am calling it via select [dbo].[getFiatProfit](600.26,'GBP', 1000.99,'BTC') as op

What am I missing?

/****** Object: UserDefinedFunction [dbo].[getFiatProfit] Script Date: 06/07/2022 11:42:26 ******/

ALTER FUNCTION [dbo].[getFiatProfit] (
    @fiatInvested float,
    @fiatInvestedCurrency nvarchar,
    @quantity float,
    @currency nvarchar
)
RETURNS float
AS
BEGIN
    declare @tmp float
    declare @result float
    declare @usdtgbp float
    IF (@fiatInvestedCurrency = 'USD')
        BEGIN
            select @tmp = [dbo].[usdtPairs].[Value] from [dbo].[usdtPairs] where usdtPairs.ID = @currency;
            select @usdtgbp = [dbo].[usdtPairs].[Value] from [dbo].[usdtPairs] where usdtPairs.ID = 'GBP';
            set @result = (((@quantity * @tmp) - @fiatInvested) / @usdtgbp);
            -- set @result = @quantity * @tmp;
        END
    ELSE
        BEGIN
            select @tmp = [dbo].[usdtPairs].[Value] from [dbo].[usdtPairs] where usdtPairs.ID = @currency;
            set @result = ((@quantity * @tmp) - @fiatInvested);
            -- set @result = @quantity * @tmp;
        END
return (@result)
END

CodePudding user response：

Your issue looks it's because your parameters are declared without a length. nvarchar defaults to a length of 1 in a lot of circumstances, so it's simply the wrong value being received. A much better data type would be char(3) which is fixed length, given that all currencies have exact three-letter names.

You should also convert this function into a Table Valued Function, which is likely to perform far better.

CREATE OR ALTER FUNCTION dbo.getFiatProfit (
    @fiatInvested float,
    @fiatInvestedCurrency char(3),
    @quantity float,
    @currency char(3)
)
RETURNS TABLE
AS RETURN

SELECT
  result = ((@quantity * u.Value) - @fiatInvested)
    / (CASE WHEN @fiatInvestedCurrency = 'USD'
        THEN 1
        ELSE
          (SELECT u2.Value FROM dbo.usdtPairs u2 WHERE u2.ID = 'GBP')
       END)
FROM dbo.usdtPairs u
WHERE u.ID = @currency;

You use it like this

SELECT t.*, fp.*
FROM YourTable t
CROSS APPLY dbo.getFiatProfit(t.fiatInvested, t.fiatInvestedCurrency, t.Qty, 'GBP') fp;