저장 프로시저와 사용자 정의 함수

같은 로직을 매번 애플리케이션에서 보내는 대신, 저장 프로시저나 함수로 DB에 미리 정의해 두면 재사용성과 보안, 성능을 모두 챙길 수 있습니다. T-SQL의 진가가 드러나는 영역입니다.

학습 목표

• CREATE PROCEDURE 로 저장 프로시저를 만들고 실행합니다.
• 입력·출력 매개변수를 다룹니다.
• 스칼라 함수와 테이블 반환 함수를 구분해 작성합니다.
• 동적 SQL의 위험과 안전한 사용법을 이해합니다.

저장 프로시저 기본

CREATE PROCEDURE usp_GetEmployeesByDept
  @department NVARCHAR(30)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT id, name, salary
  FROM Employees
  WHERE department = @department
  ORDER BY salary DESC;
END;

-- 실행
EXEC usp_GetEmployeesByDept @department = 'Dev';

💡 TIP — 프로시저 시작에 SET NOCOUNT ON; 을 넣으면 "(N rows affected)" 메시지 전송이 줄어 불필요한 네트워크 왕복이 감소합니다. 관례처럼 넣어 두면 좋습니다.

입력/출력 매개변수

CREATE PROCEDURE usp_AddEmployee
  @name       NVARCHAR(50),
  @department NVARCHAR(30) = 'Unassigned',   -- 기본값
  @newId      INT OUTPUT                       -- 출력 매개변수
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO Employees (name, department)
  VALUES (@name, @department);

  SET @newId = SCOPE_IDENTITY();   -- 방금 생성된 ID 반환
END;

DECLARE @id INT;
EXEC usp_AddEmployee @name = '홍길동', @newId = @id OUTPUT;
SELECT @id AS 생성된ID;

사용자 정의 함수

스칼라 함수 (단일 값 반환)

CREATE FUNCTION fn_AnnualSalary (@monthly DECIMAL(12,2))
RETURNS DECIMAL(14,2)
AS
BEGIN
  RETURN @monthly * 12;
END;

SELECT name, dbo.fn_AnnualSalary(salary) AS 연봉
FROM Employees;

테이블 반환 함수 (인라인 TVF)

CREATE FUNCTION fn_EmployeesByDept (@dept NVARCHAR(30))
RETURNS TABLE
AS
RETURN (
  SELECT id, name, salary
  FROM Employees
  WHERE department = @dept
);

SELECT * FROM dbo.fn_EmployeesByDept('Dev');

⚠️ 주의 — 스칼라 함수를 SELECT 의 많은 행마다 호출하거나 WHERE 조건에 쓰면 행마다 실행되어 매우 느려질 수 있습니다. 가능하면 인라인 테이블 반환 함수나 일반 식으로 대체하세요.

프로시저 vs 함수

구분	저장 프로시저	함수
호출	EXEC	SELECT 안에서 식처럼
반환	결과셋·OUTPUT·상태값	단일 값 또는 테이블
데이터 변경	가능(INSERT/UPDATE 등)	불가(읽기 전용)
트랜잭션 제어	가능	불가

동적 SQL 주의

문자열로 쿼리를 조립해 실행할 수 있지만, 사용자 입력을 그대로 이어 붙이면 SQL 인젝션에 노출됩니다.

-- ❌ 위험: 문자열을 그대로 이어 붙임
SET @sql = N'SELECT * FROM Employees WHERE name = ''' + @name + '''';
EXEC(@sql);

-- ✅ 안전: sp_executesql + 매개변수 바인딩
SET @sql = N'SELECT * FROM Employees WHERE name = @n';
EXEC sp_executesql @sql, N'@n NVARCHAR(50)', @n = @name;

sp_executesql 로 값을 매개변수로 넘기면 입력이 코드로 해석되지 않아 안전하고, 실행 계획 재사용에도 유리합니다.

요약

• 저장 프로시저는 EXEC 로 호출하며 데이터 변경·트랜잭션 제어가 가능합니다.
• OUTPUT 매개변수로 결과값을, 매개변수 기본값으로 선택 인자를 표현합니다.
• 스칼라 함수는 단일 값, 인라인 TVF는 테이블을 반환합니다.
• 스칼라 함수를 행마다 호출하면 성능이 크게 떨어질 수 있습니다.
• 동적 SQL은 sp_executesql 매개변수 바인딩으로 인젝션을 막습니다.

연습문제

1. 부서명을 받아 해당 부서 직원을 급여순으로 반환하는 프로시저를 작성하세요.
2. 직원을 추가하고 생성된 ID를 OUTPUT 으로 돌려주는 프로시저를 만드세요.
3. 월급을 받아 연봉을 반환하는 스칼라 함수를 작성하세요.
4. 사용자 입력으로 검색하는 동적 SQL을 sp_executesql 로 안전하게 바꿔 보세요.

힌트 — 2번은 SCOPE_IDENTITY(), 4번은 sp_executesql @sql, N'@n NVARCHAR(50)', @n = @input 형태입니다.