MSSQL 저장 프로시저 사용법(실행) 및 예제 (TRY…CATCH 등)

MS SQL에서 저장 프로시저는 쿼리문 집합을 일괄로 처리할 수 있게 하는 프로그래밍 기능을 합니다. 클라이언트로부터 매번 쿼리문 받아 처리하는 것보다 저장 프로시저로 작성한 후 저장 프로시저만 호출하는 것이 훨씬 효율적입니다. 예시로 저는 ERP 시스템에서 간단한 저장/수정/삭제/조회는 C#에서 처리하고 복잡한(배치) 로직 및 조회 화면에서는 주로 저장 프로시저로 처리합니다.   

그럼 간단한 예제를 통해서 저장 프로시저 사용법에 대해서 알아보죠.

포스트 기준

– Microsoft SQL Server 2019

– SSMS 18.11

저장 프로시저를 생성하는 방법은 SSMS 툴로 생성하는 방법과 쿼리문으로 생성하는 방법이 있는데 쿼리문으로 생성하는 방법에 대해서만 알아보죠.

저장 프로시저 사용 예제

1. SSMS 툴로 저장 프로시저 생성

▶ 데이터베이스 → 프로그래맹 기능 → 저장 프로시저 → 저장 프로시저(S)

2. 저장 프로시저 사용 예제 1 : 저장/수정/삭제

▶ TRY…CATCH 구문은 필요에 따라서 추가하시면 됩니다.

/* 학번, 성명, 과, 학년을 받아 학생정보에 저장 */
CREATE PROC USP_BS_DEMO_1 (
   @P_ID	NVARCHAR(50),        -- 학번
   @NAME	NVARCHAR(50),        -- 성명
   @DEPART	NVARCHAR(50),        -- 과
   @GRADE	SMALLINT,            -- 학년
   @RETURN	SMALLINT	OUTPUT   -- OUTPUT 변수 : '1'이면 정상 처리 
) 
AS
     
SET NOCOUNT ON   -- 영향을 받은 행 수를 나타내는 메시지 출력하지 않음
     
/* TRY...CATCH 구문을 사용 : 반드시 사용할 필요는 없고 필요시 사용 */
BEGIN TRY
        
   BEGIN TRANSACTION
        
   INSERT INTO BS_DEMO_8_1 (P_ID, NAME, DEPART, GRADE) 
   VALUES (@P_ID, @NAME, @DEPART, @GRADE)
     
   COMMIT
        
   SET @RETURN = 0  -- 정상인 경우 1을 넘겨 줌
     
END TRY
     
     
BEGIN CATCH
     
   IF @@TRANCOUNT > 0
      ROLLBACK
     
   SET @RETURN = -1   -- 비정상인 경우는 -1을 넘겨 줌
        
END CATCH
     
GO

▶ 저장 프로시저 호출 사용 예제

-- 저장 프로시저 호출
EXEC USP_BS_DEMO_1 '1004', '홍길동4', '컴퓨터공학과', 1, ''
     
-- 내역 확인
SELECT * FROM BS_DEMO_8_1

3. 저장 프로시저 사용 예제 2 : 조회

▶ 테이블 변수 사용

/* 학번을 받아 학생정보 조회 */
CREATE PROC USP_BS_DEMO_2 (@IN_P_ID NVARCHAR(5))
AS
BEGIN
     
   /* 테이블 변수 선언 */
   DECLARE @TEMP TABLE (
      P_ID        NVARCHAR(50),
      NAME        NVARCHAR(50),
      DEPART      NVARCHAR(50),
      GRADE       SMALLINT,
      S_ID        NVARCHAR(50),
      SUBJECT     NVARCHAR(50),
      SCORE       SMALLINT,
      CLASS       NVARCHAR(10)
   )
     
   -- 테이블 변수에 저장 작업
   INSERT INTO @TEMP
   SELECT A.P_ID,      -- 학번
          B.NAME,      -- 성명
          B.DEPART,    -- 학과
          B.GRADE,     -- 학년
          A.S_ID,      -- 과목코드
          C.SUBJECT,   -- 과목
          A.SCORE,     -- 점수
          A.CLASS      -- 등급
     FROM BS_DEMO_8_3 A
          INNER JOIN BS_DEMO_8_1 B ON A.P_ID = B.P_ID
          INNER JOIN BS_DEMO_8_2 C ON A.S_ID = C.S_ID
    WHERE A.P_ID = @IN_P_ID   -- 학번 받은 변수
     
-- 해당되는 데이터 조회
SELECT * FROM @TEMP
     
END

▶ 저장 프로시저 호출 사용 예제

-- 저장 프로시저 호출
EXEC USP_BS_DEMO_2 '1001'

4. 생성된 저장 프로시저 확인

함께 보면 좋은 게시글

• MSSQL 동적 쿼리 사용법, 저장 프로시저 및 함수 활용
• MSSQL INSERT EXEC 프로시저 실행 결과 테이블에 저장하기
• MSSQL 테이블 변수 생성 및 사용 방법
• MSSQL 트리거(TRIGGER) 사용법(생성) 및 예제
• MSSQL 변수 – 선언(DECLARE), 대입(SET), 반환(SELECT)

이 글이 도움이 되셨다면 공유 부탁 드립니다.