Menu
Can OPTION (RECOMPILE) be used in an Inline Table Valued Function?
I try to use it but it gives me error. If it cannot be used what other way is advisable to recompile a Inline Table Valued Function?
My code will be like
ALTER FUNCTION [dbo].[fn_abcwork]
(
@Date date
, @id VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
SELECT a.name
, a.age
, a.title
, b.work
from tbl_abc a
left outer join tbl_emp on a.id=b.id
464
Taking the definition of what a user defined function (UDF) is from Books Online, an inline table-valued function (iTVF) is a table expression that can accept parameters, perform an action and provide as its return value, a table.
A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table , you can use a table-valued function anywhere in SQL that you can use a table.
WITH RECOMPILE Option If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure.
Inline table valued function refers to a TVF where the function body just contains one line of select statement. There is not return variable. Multi-statement table valued function refers to a TVF where it has a return table variable. Inside the function body, there will be statements populating this table variable.
Inline Table Valued Functions are expanded out into the calling query so nothing is ever actually called. Put the OPTION(RECOMPILE) on the query that is using the function.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
