question

JackFrost avatar image
JackFrost 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!
sql-server-2012scalarin-line-table-valued-function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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,....)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.