For a multistatement table-valued function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that build and insert rows into the table that will be returned.
/* Code: MTV function containing multiple statements */
FROM dbo.MyFn_MultiStmnt()
** Following function body code would give ERROR when used with an Inline Table Valued Function.
(
column2 int
)
AS
BEGIN
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey
FROM dbo.DimEmployee
ORDER BY 1
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey+1
FROM dbo.DimEmployee
ORDER BY 1
RETURN
END
- Explicitly defines the structure of the table to return.
- Defines column names and datatypes in the RETURNS clause.
- Able to house more complicated and numerous T0SQL logic blocks.
/* Code: MTV function containing multiple statements */
CREATE FUNCTION dbo.MyFn_MultiStmnt()
RETURNS @temp_table table
(
column2 int
)
AS
BEGIN
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey
FROM dbo.DimEmployee
RETURN
END
/*Call above MTV function*/
SELECT * FROM dbo.MyFn_MultiStmnt()
** The function contains more than one statement in the function body. Unlike Inline Table Valued function, where only one statement can be written, in Multistatement Table Valued function more than one statement can be written.
** Following function body code would give ERROR when used with an Inline Table Valued Function.
/* Code:MTV function containing multiple statement*/
CREATE FUNCTION dbo.MyFn_MultiStmnt()
RETURNS @temp_table tableCREATE FUNCTION dbo.MyFn_MultiStmnt()
(
column2 int
)
AS
BEGIN
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey
FROM dbo.DimEmployee
ORDER BY 1
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey+1
FROM dbo.DimEmployee
ORDER BY 1
RETURN
END