Call UDF with many parameters

Question

I am creating an import process where I ultimately need a SELECT of several fields along with a computed value. The logic of the computed value is rather complex, I'll need to do it in a UDF rather than in the SELECT itself.

The problem is that the computed value is determined by the contents of 30-40 columns... What I've considered:

A monster function with 30-40 input parameters.
Write a SP or C# app to loop through the selected data. This option is likely not as future-proof as the others.
Passing the entire record's contents as JSON as a single parameter and pull the appropriate fields in my UDF. I'd need to combine columns and JSON in a select as shown here.

Any ideas for a better solution? I'm leaning towards the last one.

To further worsen this situation is that the data is not on the server that I plan on running the UDF, and that server is older (does not support FOR JSON clause). But I can clumsily get around this part with OpenQuery().

Lukasz Szozda · Accepted Answer · 2017-12-02 15:31:44Z

1

Instead of passing multiple parameters you could pass singleTVP (table valued parameter).

Approach 1:

CREATE TYPE MyTableType AS TABLE   
( param1 VARCHAR(50)  
, param2 INT
, param3 DATE );  

CREATE FUNCTION dbo.my_func(@tvp_params MyTAB)
RETURNS TABLE
AS RETURN (
     SELECT ...
     FROM ...
     JOIN @tvp_params ...
);

Approach 2:

CREATE TYPE myEAVTableType AS TABLE
(  param_id    INT
  ,param_name VARCHAR(128)
  ,param_value SQL_VARIANT);

And inside of function do some sort of PIVOT.

answered Dec 2, 2017 at 15:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call UDF with many parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related