Passing 'current' row to a function in SQL Server

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

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

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.

You can use a table-valued function to pass a row to a function (SQL Server 2008 and later). I think a regular scalar function would be a better alternative in your case though.