Passing the "current" function string to SQL Server

Question

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

I have a table for which I can not change the structure. It registers cash deposits at terminals like ATM and has one column for each denomination of cash. Now, when I need the full value of the deposit, I need the code:

(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 this common value, but for any row, not for the whole query, so my function will look something like this:

 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

Should the solution be a function? Or a limiting factor that you cannot change the structure of the table? You can write a table view that adds a column with the full 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 modular code, but definitely not possible with TSQL functions.

The only way to do this using UDF is to pass strings to the PK and force the function to search for it (inefficiently) or pass all values ​​individually (cumbersome).

It might be worth the suggestion for a Microsoft Connect site, although I just saw Brett’s answer , and indeed, views or computational columns really give this functionality.

Answer 3

You can use a table 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.