Multi-statement table-valued functions in Sql Server

Multi-statement table-valued function(MSTVFs)은 테이블을 반환 값으로 돌려준다. 그리고 이 반환되는 테이블의 구조는 사용자에 의해서 정의가 된다.

 

MSTVFs는 하나 이상의 Statement를 포함할 수 있다. 

 

# MSTVFs Syntax

 

Create Function Multistatement_TableValued_Function_Name 

(

    @param1 DataType,

    @param2 DataType,

    ..............

    @paramN DataType

)

Returns

@OutputTable Table

(

    @Column1 DataTypeForColumn1,

    @Column2 DataTypeForColumn2

) 

As

Begin

    -- Function Body

    return

End

 

# MSTVFs와 inline table-valued function 차이점

1. return table structure를 선언한다.

2. Begin/End 블록을을 시작과 끝에 적는다.

3. 하나 이상의 Statement를 함수 body에 포함시킬 수 있다. 

4. 반드시 Return Operator를 사용해야 한다. 

# MSTVFs와 inline table-valued function Syntax 차이점 

Muti-Statement Tavble-Valued Function	Inline Table-Valued Function
Create Function Multistatement_TableValued_Function_Name  (     @param1 DataType,     @param2 DataType,     ..............     @paramN DataType ) Returns @OutputTable Table (     @Column1 DataTypeForColumn1,     @Column2 DataTypeForColumn2 )  As Begin     -- Function Body     return End	Create Function TableValues_Function_Name (     @param1 Datatype,     @param2 Datatype,     ..............     @paramN DataType ) returns Table  As Return (     Select Statement )

# 작성예

CREATE FUNCTION dbo.UdfGetProductsScrapStatus

(

    @ScrapComLevel INT -- 파라메터 선언

)

RETURNS @ResultTable TABLE -- Return 테이블 정의, 이 테이블은 함수 Body안에서 생성되고 업데이트되어 반환됨.  

(

    ProductName VARCHAR(50), -- 테이블 컬럼 열거

    ScrapQty FLOAT,

    ScrapReasonDef VARCHAR(100),

    ScrapStatus VARCHAR(50) 

) AS BEGIN -- 블록 시작

        INSERT INTO @ResultTable

            SELECT PR.Name, SUM([ScrappedQty]), SC.Name, NULL

                FROM [Production].[WorkOrder] AS WO

                        INNER JOIN

                        Production.Product AS PR

                        ON Pr.ProductID = WO.ProductID

                        INNER JOIN Production.ScrapReason AS SC

                        ON SC.ScrapReasonID = WO.ScrapReasonID

                WHERE WO.ScrapReasonID IS NOT NULL

                GROUP BY PR.Name, SC.Name

UPDATE @ResultTable

            SET ScrapStatus =

            CASE WHEN ScrapQty > @ScrapComLevel THEN 'Critical'

            ELSE 'Normal'

            END

        

RETURN  -- 반드시 Return을 사용해야 Output 테이블(@ResultTable)이 반환됨.

END -- 블록 끝

 

# MSTVFs 실행 

SELECT ProductName AS [Product Name],

          ScrapQty AS [Scrap Quantity] ,

          ScrapReasonDef AS [Scrap Reason],

          ScrapStatus AS [Scrap Status]

FROM dbo.UdfGetProductsScrapStatus(200)

 

위와 같이 단독으로 테이블처럼 사용할 수 있고 또 From Table as A cross apply MSTVFs와 같이 사용할 수도 이다. 

 

# 결론

1. MSTVFs는 retun/output 테이블을 함수 Body내에서 수정 가능하므로 복잡한 결과 셋을 생성할 수 있다.

2. select 문에 사용하거나 다른 테이블들과  join 시켜 사용할 수 있다.