[DB] MSSQL 사용자정의 함수 만들기

MSSQL 사용자 정의 함수사용법에 대해 알아보겠습니다. 사용자 함수는 언제 사용 하면 좋을까요? 프로그램 할때 만드는 함수와 같이 MSSQL서버상의서 함수를 만들어서 모듈식 프로그램이 사용가능합니다. 반복적으로 어떤 기능을 하는 부분을 함수로 만들어 두면 유용하게 사용 할 수 있습니다.저장프로시저와 마찬가지로 사용자 정의 함수는 TSQL코드의 컴파일 비용을 줄이 수 있습니다. 
MSDN의 함수 Syntax를 보면 아래와 같습니다. 테이블 생성하는 스크립트와 유사하며 CREATE, ALTER문을 사용해서 함수 생성 및 수정이 가능합니다. DROP문을 사용해서 해당함수를 삭제할 수 있습니다. 

그럼 이제 실제로 함수를 만들어 보겠습니다. 

1. 사용자 함수생성방법 
AdventureWorksLT2019 데이터베이스로 테스트 하겠습니다. 웹서비스 개발시 Json전문으로 처리를 많이하는데 고객정보를 ID값을 넣으면 고객ID,성,이름 정보를 Json파일로 만들어주는 함수를 만들어 보겠습니다. Create Function문을 사용해서 함수를 생성합니다. CustomerID값을 인자로 받아서 해당ID값에 상세정보를 json형태 데이터로 리턴해보겠습니다. 

USE [AdventureWorksLT2019]
GO
 
CREATE FUNCTION [dbo].[ufnGetCustomerInformation_json]
(
    @CustomerID int
)
RETURNS VARCHAR(max)
AS 
BEGIN
    RETURN 
    (
        SELECT 
            CustomerID, 
            FirstName, 
            LastName
        FROM [SalesLT].[Customer] 
        WHERE [CustomerID] = @CustomerID
        FOR JSON AUTO
    )
END  

2. 함수 사용방법 
함수사용법은 간단합니다. 아래 쪽 내용과 같이 [dbo].[ufnGetCustomerInformation_json] 와 같이 함수명 입력 후 파라메터값(1)을 입력하면됩니다. 

USE [AdventureWorksLT2019]
GO
 
SELECT [dbo].[ufnGetCustomerInformation_json] (1)

함수를 호출하면 결과값이  [{"CustomerID":1,"FirstName":"Orlando","LastName":"Gee"}] 와 같이 Json전문으로 결과값이 나오는 부분을 확인 할 수 있습니다. 

이번에는 CustomerAddress테이블 조회시에 ufnGetCustomerInformation_json함수를 호출해서 고객상세정보를 json형태의 문자열로 항목을 추가해보도록 하겠습니다. Select문에 ufnGetCustomerInformation_json(CustomerID) 컬럼을 추가하면 고객상세정보를 아래와 같이 출력되는 부분을 확인 할 수 있습니다. 

이와 같이 자주 사용하는 함수를 사전에 만들어서 사용하면 모듈형태로 사용 할 수 있기때문에 유용하게 사용 할 수 있습니다. 적절하게 사용하시기 바랍니다.

참고 
https://docs.microsoft.com/ko-kr/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16
mssql json  :  https://sosopro.tistory.com/136