MSSQL 테이블 반환 함수 및 스칼라 반환 함수 사용법.

MSSQL 함수는 크게 스칼라 반환 함수, 인라인 테이블 반환 함수, 다중문장 테이블 반환 함수로 나눠서 data를 반환받을 수 있습니다.

Scalar, 테이블 반환 함수 특징

스칼라 반환 함수(Scalar function) 특징

스칼라함수 단일 스칼라값을 Rerun.
단일 스칼라값을 반환하는 함수를 보통 스칼라 반환 함수하고 합니다.
스칼라값은 단일 값을 반환한다고 생각하면 이해하기 쉽습니다.
예를 들면 숫자 100, 문자로 San Diego, PROCEDURE에서 OUT 변수와 비슷합니다.

테이블 반환 함수(table-valued function) 특징

인라인 테이블 반환 함수.
반환되는 column을 select 절에서 결정해서 반환할 수 있습니다.
한 개의 QUERY 문 작성해야 하므로 다중문장일 때는 사용할 수 없습니다.
제어문을 사용할 수 없습니다.
영어로는 inline table-valued function입니다.

다중문장 테이블 반환 함수.

여러 문장을 사용하여 반환 값을 지정할 수 있습니다.
begin, end 구분 안에 QUERY 문을 작성해야 합니다.
IF 문뿐만 아니라 대부분의 제어문을 사용할 수 있어 분기 처리가 가능합니다.
반환되는 table을 정의해야 합니다.
마지막에 return 문으로 종료해야 합니다.
예를 들어 테이블에 5개의 칼럼을 선언했다고 하면은 5개 칼럼의 데이터만 반환받을 수 있습니다.
영어로는 Multi-statement table-valued function(MSTVFs)입니다.

스칼라, 테이블 반환 함수 사용법

스칼라 및 테이블 반환 함수 차이점 설명.

스칼라 반환 함수(Scalar function) 사용법.

-- DROP FUNCTION AurumGuide_scalarReturn;
CREATE FUNCTION AurumGuide_scalarReturn (
   @PARAM1 varchar(20),
   @PARAM2 varchar(20) 
)
RETURNS varchar(100)
AS
BEGIN
    declare @returnStr varchar(100);
    set @returnStr  = @PARAM1 + ': ' + @PARAM2;
    RETURN @returnStr
END; 
 
-- EXCUTE 
SELECT DBO.AurumGuide_scalarReturn('AurumGuide','Scalar Return Func') as 'scalar' ;

인라인 테이블 반환 함수 사용법.

-- 1. CREATE TABLE  
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForTableReturn;
CREATE TABLE AurumGuideForTableReturn (
    UserId         INT,
    UserNm         VARCHAR(255) NULL,
    DeptId         INT
);
 
INSERT INTO dbo.AurumGuideForTableReturn(UserId,UserNm,DeptId) 
VALUES (272, N'Ken',100)
      ,(273, N'Brian',200)	 
      ,(276, N'Linda',200)
      ,(277, N'Lee',200);
 
--drop FUNCTION fn_AurumGuideForInTableReturn;
go
--인라인 테이블 반환 함수 예제.

CREATE FUNCTION fn_AurumGuideForInTableReturn (@DeptId INT)
RETURNS TABLE
as 
RETURN 
(  
   SELECT UserId,UserNm, 'Aurum' as 'TableReturnCol'
     FROM AurumGuideForTableReturn t1
    WHERE t1.DeptId = @DeptId
);
go
-- EXCUTE
SELECT UserId,UserNm,TableReturnCol
  FROM fn_AurumGuideForInTableReturn(200);

다중문장 테이블 반환 함수 사용법.

--다중문장 테이블 반환 함수 예제. 
--DROP FUNCTION fn_AurumGuideForMulTableReturn; 
CREATE FUNCTION fn_AurumGuideForMulTableReturn  (@UserId int)
RETURNS @Return_AurumGuide  TABLE
(	
    UserId              int,
    UserNm varchar(255) NULL 
) AS
BEGIN
   IF(@UserId = '')
   BEGIN
      INSERT @Return_AurumGuide
      SELECT UserId,UserNm
      FROM AurumGuideForTableReturn af;
   END 
   ELSE 
   BEGIN
      INSERT @Return_AurumGuide
      SELECT UserId,UserNm
        FROM AurumGuideForTableReturn af	 
       WHERE af.UserId  =  @UserId ;
   END
   RETURN;
END;

 -- EXCUTE 
SELECT * 
  FROM fn_AurumGuideForMulTableReturn(''); 	 
-- EXCUTE 
SELECT * 
 FROM fn_AurumGuideForMulTableReturn('273');