[MSSQL] USP(Procedure) & UFN(Function)

USP : User-Defined Stored Procedure

UFN 또는 UDF : User-Defined Function

 

사용자가 필요에 의해 정의(User-Defined)하여 사용하는 쿼리 집합이자 절차.

파라미터와 리턴값을 지정할 수 있고 둘 다 구조는 같다.

프로시저는 서버(DB)상에서 처리되고 함수는 클라이언트에서 처리되는데 서버상에서 처리되는 프로시저가 속도가 더 빠르다. 

 

차이점

Procedure	Function
서버(DB) 상에서 처리	Client 상에서 처리
트랜잭션 가능 (TRY-CATCH, CUD, 프로시저 호출 가능)	트랜잭션 불가능 (TRY-CATCH, CUD, 프로시저 호출 불가능)
리턴 필수 아님	리턴 필수

 

---

생성

-- USP
CREATE PROCEDURE DBO.USP명 (
    @InParameter		NVARCHAR(30), -- 파라미터타입
    @OutParameter		NVARCHAR(20) OUTPUT,
    @ERRMSG             	NVARCHAR(200) OUTPUT
) AS

BEGIN
    SET NOCOUNT ON; -- 'n개 행이 적용됨' 메시지 표시X(성능 향상됨)
    -- DECLARE @Variable		NVARCHAR(10) --필요 시 변수 선언
    
    BEGIN TRY  -- 트랜잭션 필요한 경우 TRY CATCH
    	BEGIN TRANSACTION
            -- 원하는 CRUD작업 추가
            UPDATE ...
            SELECT @OutParameter = Target_Col FROM TABLE_A WHERE Search_Col = @InParameter 
        COMMIT TRANSACTION
        RETURN 1
    END TRY
    BEGIN CATCH
    	-- 원하는 내용 추가
        SET @ERRMSG = ERROR_MESSAGE()
        ROLLBACK TRANSACTION
        RETURN -1
    END CATCH
    
    SET NOCOUNT OFF;
END
GO


-- UFN
CREATE FUNCTION DBO.UFN명 (
    @InParameter		NVARCHAR(30) -- 파라미터타입
)
RETURNS NVARCHAR(5) -- 리턴타입, RETURNS TABLE 테이블도 가능
AS
-- RETURN ( SELECT문 ) -- 테이블 리턴일 경우
BEGIN
    DECLARE @OutParameter	NVARCHAR(10)
    SELECT @OutParameter = Target_Col FROM TABLE_A WHERE Search_Col = @InParameter 
    RETURN @OutParameter
END
GO

 

수정/삭제

수정은 생성문에서 CREATE 를 ALTER 로 바꾸고 수정하고 싶은 부분을 수정한 후 실행하면 된다.

CUD는 트랜잭션 잊지 말자

-- 수정
ALTER PROCEDURE ...
ALTER FUNCTION ...

-- 삭제
DROP PROCEDURE USP명
DROP FUNCTION UFN명

 

조회 & 실행

-- USP 조회
SP_HELPTEXT USP명

-- 조회
SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' -- 또는 FUNCTION
AND ROUTINE_NAME LIKE '%USP명%' -- 또는 UFN명
-------------------------------------------------------------

-- USP 실행
DECLARE @OutParameter VARCHAR(20), @ERRMSG VARCHAR(200) -- OUTPUT 값 저장할 변수
EXEC USP명 'InParameter값', @OutParameter OUTPUT, @ERRMSG OUTPUT
SELECT @OutParameter -- 값 찍어보기

-- UFN 실행
SELECT DBO.UFN명('InParameter값') 리턴별칭