0

My question is similar to this.

I made a scalar function like follows:

CREATE FUNCTION [dbo].[MyFunction](@table [TableModel] READONLY)
RETURNS DECIMAL(18, 6) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX), @params NVARHCAR(MAX), @value DECIMAL(16, 8);
    SELECT @sql = formula, @params = params FROM formulas WHERE id = (SELECT TOP 1 id_formula FROM @table)
    EXEC sp_executesql @sql, @params, @table=@table, @value=@value OUTPUT;
    RETURN @value;
END

Where a formula SQL could be something like:

SELECT @value = SUM(value) / AVG(value) FROM @table

And it could have more columns if needed.

The model table looks like so:

CREATE TYPE [dbo].[TableModel] AS TABLE(
    [formula] INT NOT NULL,
    [value] DECIMAL(16, 8) NOT NULL
)

And I want to use it like so:

SELECT  od.id, od.col1, od.col2, 
        dbo.MyFunction((SELECT id.formula, id.value FROM #raw_data id WHERE id.id = od.id)) as result
FROM    #data od
GROUP BY od.id, od.col1, od.col2

Where the ID unique and multiple rows will have the same id.

Basically, what I'm trying to do in a single query I want to call a function that has a table parameter. But I want this table to be a subquery.

I'm aware that you can call the function with a table variable as mentioned in this answer.

Is this possible in any way? I'm having this error at executing:

Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Bruno Ramirez
  • 46
  • 1
  • 8
  • Expression `(SELECT id.value FROM #raw_data id WHERE id.id = od.id)` is not of the `[TableModel]` type. Sql server has no way to cast a derived table to a user defined table type. You want a [user defined aggregate](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-ver15) – Serg Nov 08 '21 at 17:51
  • Your function doesn't even use the `@table` variable, it's not clear exactly what you are trying to do but you probably need a table-valued function you can use with `apply` – Stu Nov 08 '21 at 17:53
  • @Stu you were right, I edited the question. Unfortunately, table-valued function is not an option. What I want is to pass a subquery into a scalar function to retrieve a single value. – Bruno Ramirez Nov 08 '21 at 18:00
  • @Serg Thanks for the reply! I'll check out the user-defined aggregations. – Bruno Ramirez Nov 08 '21 at 18:01
  • A table-valued function is always the preferred choice, why is it not an option? – Stu Nov 08 '21 at 18:02
  • @Stu I edited my question. I don't think that table-value functions fits in this scenario where I try to execute some SQL with ```sp_executesql```. – Bruno Ramirez Nov 08 '21 at 18:56
  • 1
    Well you can't use dynamic SQL in a function either. If you describe what you're trying to achieve instead of describing how you're trying to achieve it, there may be a workable approach. – David Browne - Microsoft Nov 08 '21 at 19:14
  • @BrunoRamirez you've changed the scenario of the question and what you have presented cannot work, it appears you have an XY problem. – Stu Nov 08 '21 at 19:17
  • @DavidBrowne-Microsoft your right, my bad. For some reason, I was able to execute a function with a sp_executesql inside of it but it was my fault from the beginning. Despite that, I was able to achieve what I was trying to do with unpivot. Thanks, everyone! – Bruno Ramirez Nov 08 '21 at 19:30
  • This question is so full of syntax errors, it's hard to know what you are trying to do. It seems you want to execute dynamic SQL, which you can't do in a function. Perhaps you should rethink your data model – Charlieface Nov 08 '21 at 21:52

0 Answers0