Using a scalar-valued function as a parameter to another SVF

0
votes

I have a handful of scalar-valued functions (which I appreciate may not be best practice, but for now, this will suffice, this is simply a development exercise) which I use within various computed columns with MS SQL tables.

I have a scenario whereby I need to use the result of one of these functions, as a parameter to another within a given computed column. For example;

(case when [DynamicQuantity] IS NULL then ([dbo].[GetFiatValue]([Currency],[Quantity])) ELSE ([dbo].[GetFiatValue]([Currency],([dbo].[getStakedQuantity]([currency])))) end)

If the given [DynamicQuantity] field is null (a column value generated by another SVF), use the [Quantity] field. However, if [DynamicQuantity] is populated, the ELSE will fire, and pass the output of the [dbo].[getStakedQuantity] SVF in place of the [Quantity] value.

Is this achievable? I can't seem to get the syntax to work and am questioning whether this is even possible?

Running it via a normal query works;

SELECT case when [DynamicQuantity] IS NULL then [dbo].[GetFiatValue]([Currency],[Quantity]) ELSE [dbo].[GetFiatValue]([Currency],[dbo].[getStakedQuantity]([currency])) end from dbo.cryptoPortfolio

But when used as a computed column formula below;

case when [DynamicQuantity] IS NULL then [dbo].[GetFiatValue]([Currency],[Quantity]) ELSE [dbo].[GetFiatValue]([Currency],[dbo].[getStakedQuantity]([currency])) end

SSMS will not accept it, with Error validating the formula for column 'FiatValue'.

sql-servertsql
So what error do you get when you try here? If you append a SELECT to that statement, the syntax is correct (though there's certainly an overuse of parenthesis (()) in my opinion). – Larnu
When applying the statement to the computed column, SSMS simply does the it has failed syntax validation checks. When running via SELECT, it does indeed work as desired against my table. To be specific, the error from SSMS is: - Error validating the formula for column 'FiatValue'. – Dms2701