In TSQL can I create a UDF which has as input a table's row?

Question

I'm looking to create what I would think is simple.

I want a user defined function which takes a row from a table as imput and returns a scalar value.

Something like

select 
    mt.Id,
    MyUDF(mt)
from M
    MyTable mt
where mt.Price > 0

I understand that I can pass in the Id to the UDF, and then lookup the values from within the UDF, but that seems like a lot of extra work.

Answer 1

No, a RDBMS has no concept of a row as a whole - only a tuple of fields. You need to either send the individual fields separately, or send an ID which can be used inside the UDF to retrieve the fields necessary from that row.

Answer 2

As marc_s said, the idea of sending a "row" as an object in T-SQL isn't present. You can send the data from the row as individual values, but not the row itself.

A computed column on the table itself would probably make more sense. If the value is directly dependent on the values present in the row (and not dependent on any external values), then this would be a solution.

For example, say I have this table:

Customer:
    CustomerID
    FirstName
    LastName
    ...

If I wanted a computed column for FullName, my table declaration would look like:

create table Customer
(
    CustomerID int identity(1, 1) primary key,
    FirstName varchar(100),
    LastName varchar(100),
    FullName as LastName + ', ' + FirstName
)

I could also add it after the fact with an ALTER statement

alter table Customer add FullName as LastName + ', ' + FirstName