MSSQL - Stored Procedure

1. Stored Procedure란?

저장 프로시저는 쿼리문들의 집합으로 여러 쿼리를 거쳐서 일괄적으로 처리할 때 사용합니다. 저장 프로시저는 보통 SP라고 축약하여 말하며 실제 DB 연동시에는 단순 쿼리문보다는 SP를 사용하여 데이터를 질의합니다.

 

2. 주요 특징

데이터를 호출하려면 그냥 SQL문으로 호출하면 되는데 왜 SP를 사용하는 것일까요? SP는 아래와 같은 특징을 가지고 있다.

 

① 먼저, SQL Server의 성능을 향상 시킬 수 있습니다.

 

저장 프로시저를 처음에 실행하면 최적화, 컴파일 단계를 거쳐 그 결과가 캐시(메모리)에 저장되게 되는데, 이후에 해당 SP를 실행하여 되면 캐시(메모리)에 있는 것을 가져와서 사용하므로 실행 속도가 빨라지게 됩니다. 그렇기 때문에 일반 쿼리를 반복해서 실행하는 것보다 SP를 사용하는게 성능적인 측면에서 좋습니다.

 

② 두번째, 유지보수 및 재활용 측면에서 좋습니다.

 

C#, Java 등으로 만들어진 응용프로그램에서 직접 SQL 문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수요조건이 발생할 때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해집니다. 또한 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 좋습니다.

 

③ 세번째, 보안을 강화할 수 있습니다.

 

사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있습니다. 실제 테이블에 접근하여 다양한 조작을 하는 것은 위험하기 때문에 실무에서는 실제로 개발자에게는 SP 권한만 주는 방식을 많이 사용합니다.

 

④ 마지막으로, 네트워크의 부하를 줄일 수 있습니다.

 

클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 됩니다. 하지만 저장 프로시저를 이용한다면 저장 프로시저의 이름, 매개변수 등 몇 글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있습니다.

 

3. 생성하기

저장 프로시저는 쿼리문을 사용하여 생성할 수 있습니다.

CREATE PROCEDURE procedure_name
--만약에 매개변수를 사용하고 싶다면 프로시저 명 다음에 선언하면 된다.
--@val1 varchar(8),
--@var2 INT
AS
sql_statement
GO;

 

생성된 저장 프로시저는 해당 DB 경로에 있는 저장 프로시저 경로에서 확인할 수 있습니다. 참고로 저장 프로시저를 생성하는 쿼리문을 매번 작성하기 귀찮다면 저장프로시저 폴더에서 새로만들기로 쿼리문 양식을 사용할 수도 있습니다.

프로시저 생성

 

4. 호출하기

생성된 저장 프로시저는 아래와 같이 호출하면 됩니다.

-- 매개변수가 없는 경우
EXEC procedure_name

-- 매개변수가 있는 경우 변수에 값을 직접 대입
EXEC procedure_name @val1 = 'test'

-- 매개변수가 있는 경우 프로시저 뒤에 변수값을 쉼표 구분으로 추가할 수도 잇다.
EXEC procedure_name 'test1', 'test2'

 

5. 수정하기

생성된 저장 프로시저를 마우스 우측 클릭 후 수정버튼을 누르면 SP 수정이 가능합니다. 쿼리문 상단을 보면 CREATE가 아닌 ALTER 로 시작하는것을 볼 수 있습니다. 수정 후 해당 쿼리문을 실행하면 수정된 저장 프로시저가 반영됩니다.

프로시저 수정

 

🗒️ SET NOCOUNT란?

 

프로시저 작성 시 속도에 대해서는 생각을 안할 수가 없다. SET NOCOUNT를 사용하면 이 프로시저의 성능을 조금이나마 개선하는데 도움을 준다. SET NOCOUNT는 쿼리문 또는 프로시저의 영향을 받은 행 수를 나타내는 메시지가 결과 집합의 일부로 반환되지 않도록 하는 것을 말한다.

MSSQL에서 프로시저를 실행하다보면 “{0개 행이 영향을 받음}” 이라는 메시지를 볼 수 있다. 이 메세지는 INSERT나 UPDATE, DELETE 처럼 테이블에 영향을 주면 출력된다. 그런데 0개 행이 영향을 받았으면 굳이 표시될 필요가 없는데 메시지는 표시되고 서버 부하만 줄 뿐이다. 이 대안책으로 SET NOCOUNT를 사용하면 된다. SET NOCOUNT ON 문구를 삽입함으로써 {0개 행이 영향을 받음} 메시지의 경우는 출력되지 않는다.

 

 

출처

[MSSQL] 저장 프로시저 (Stored Procedure) 란?