asked
Scalar UDF convert to iTVF
Hi, I have a function that returns money... > CREATE FUNCTION functionName( > @a_principal money, > @a_from_date datetime, > @a_to_date datetime, > @a_rate float ) RETURNS money AS BEGIN > > DECLARE @v_dint money > > set @v_dint = set > @v_dint = round(@v_dint, 2) > > RETURN @v_dint > END GO Grant execute on functionName to another_user Go Im just wondering if this is possible to be converted to iTVF? I've tried doing this but I got an error: > CREATE FUNCTION fnGetInterest ( > @a_principal money, > @a_from_date datetime, > @a_to_date datetime, > @a_rate float ) RETURNS TABLE AS RETURN SELECT returnMoney = > computation_here GO > Grant execute on functionName to > another_user Go **ERROR:** > Msg 4606, Level 16, State 1, Line 2 > Granted or revoked privilege EXECUTE > is not compatible with object. This function is used like this: > update table_name set interest = > functionName(col1,col2...) where... Thanks in advance!
You do not grant EXECUTE permissions on table-valued functions, you grant SELECT. Then in your update statement you reference this TVF and use the column names from it, so something like update table_name set interest = f.returnMoney from table_name cross apply dbo.fnGetInterest (col1, col2,....)
14 People are following this question.
