Passing 'current' row to a function in SQL Server

Question

I'm not quite sure how to ask this, but let me ask by example:

I have a table beyond that I cannot change structure on. It records cash deposits at ATM-like terminals, and has one column for each denomination of monetary note. Right now when I need the total value of a deposit I need code like this:

(rd.Count_R10 * 10) + (rd.Count_R20 * 20) + (rd.Count_R50 * 50) + (rd.Count_R100 * 100) + (rd.Count_R200 * 200)

I would like to write a T-SQL function that gives me that total value, but for any row, not for an entire query, so my function would be something like:

CREATE FUNCTION DepositTotal
(
    @row ????
)
RETURNS money
AS
BEGIN   
    RETURN (row.Count_R10 * 10) + (row.Count_R20 * 20) + (row.Count_R50 * 50) + (row.Count_R100 * 100) + (row.Count_R200 * 200)
END

Then I would call it something like:

select
      DepositDate
    , DepositTotal(thisRow)
    , BatchId
from Deposits

Answer 1

Does the solution have to be a Function? Or is the limiting factor that you can't alter the structure of the table? You could write a view over the table that adds a column with your total value..

CREATE VIEW DepositsWithTotal

select
  Deposits.* -- Expand this - I just don't know your schema
, DepositTotal = (Count_R10 * 10) + (Count_R20 * 20) + (Count_R50 * 50) + (Count_R100 * 100) + (Count_R200 * 200)

from Deposits

Then just select from DepositsWithTotal instead of Deposits

Answer 2

This would be useful for modularising code but definitely isn't possible with TSQL functions.

The only way of doing it using a UDF would be to pass in the PK of the row and get the function to look it up (inefficient) or pass in all values individually (cumbersome).

Perhaps worth a suggestion for the Microsoft Connect Site though I've just seen Brett's answer and indeed Views or Computed Columns do seem to give this functionality.