MSSQL - 사용자 저장 프로시저의 사용 방법(생성/호출/수정/삭제) (Stored Procedure)

이 포스팅은..

사용자 저장 프로시저에 대한 사용방법을 설명하는 포스팅입니다.

---

저장 프로시저에 대한 자세한 설명은 아래의 포스팅을 참고해주세요.

• MSSQL - 저장 프로시저의 개념, 장점, 실행 과정 (Stored Procedure)

MSSQL - 저장 프로시저의 개념, 사용 방법(생성/호출/수정/삭제) (Stored Procedure)

 

사용자 저장 프로시저의 매개변수 활용 방법은 아래의 포스팅을 참고해주세요.

• MSSQL - 프로시저 매개변수(IN/OUT) 입력, 출력 활용하기

MSSQL - 프로시저 매개변수(IN/OUT) 입력, 출력 활용하기

 

 MS-SQL이 아닌 MySQL의 프로시저 사용법이 궁금하다면 아래의 포스팅을 참고해주세요.

• MySQL - 프로시저 문법(생성, 수정, 삭제, 호출), 프로시저 확인, 사용 예제

MySQL - 프로시저 문법(생성, 수정, 삭제, 호출), 프로시저 확인, 사용 예제

---

사용자 프로시저 생성하기

프로시저 생성 구문

• CREATE PROCEDURE  키워드 뒤에 프로시저명 입력
• 프로시저가 수행 할 내용 작성

CREATE PROCEDURE -- 프로시저명
	-- @매개변수 데이터타입
AS
BEGIN
	-- SQL문
END

---

예시) 특정 문자열이 들어간 사용자의 이름 찾는 프로시저 생성하기

-- 프로시저 search_name 정의
CREATE PROCEDURE search_name
	-- 매개변수 userName, 문자열
	@userName nvarchar(10)
AS
BEGIN
	-- 수행 결과의 영향 받은 행의 수를 반환하지 않음 (성능 최적화) 
	SET NOCOUNT ON;
	-- 매개변수의 문자열이 들어간 이름 검색 like %keyword%
	SELECT * from testTable where testName like '%'+@userName+'%';
END

-- 쿼리를 실행(F5)하여 프로시저 생성

---

+ SET NOCOUNT란?

• MSSQL(SSMS)에서 프로시저 생성시 기본으로 있는 옵션
• ON/OFF로 설정 가능
  • OFF : 기존의 쿼리 실행시 '영향 받은 행의 수'의 메시지가 반환되어 사용자에게 출력
  • ON : 쿼리 실행 결과에 해당 반환 메시지가 중요하지 않을 경우 반환하지 않도록 설정
• 이는 데이터베이스의 성능을 조금이라도 더 좋게하기 위하여 사용

---

프로시저 호출

• 테이블의 데이터 확인

select * from testTable

 

• "Bye" 문자열이 포함된 이름 검색
  • Eunbyeol

-- 사전에 정의한 사용자 저장 프로시저 호출 (search_name)
-- 매개변수(@userName)에 "Bye"라는 문자열 입력
EXEC search_name "Bye";

반응형

---

사용자 프로시저 수정하기

프로시저 수정 구문

• 기존 CREATE 구문과 형식이 동일
• 선언이 CREATE가 아닌 ALTER로 실행

-- 기존의 정의되어 있는 프로시저 수정
ALTER PROCEDURE -- 프로시저명
/* 수정 내용 작성
    @parameter dataType
AS
BEGIN
    Query
END
*/

---

예시) 이름 검색 프로시저를 지역 검색으로 수정하기

-- 프로시저 수정
ALTER PROCEDURE search_name
	-- 매개변수 변경 (기존 @userName)
	@userAddr nvarchar(10)
AS
BEGIN
	-- 수행 결과의 영향 받은 행의 수를 반환하지 않음 (성능 최적화)
	SET NOCOUNT ON;
	-- 매개변수의 문자열이 들어간 지역 검색 like %keyword%
	SELECT * from testTable where testAddr like '%'+@userAddr+'%';
END

-- 쿼리를 실행(F5)하여 프로시저 생성

---

수정된 프로시저 호출

• 테이블의 데이터 확인

select * from testTable

 

• "Inc" 문자열이 포함된 지역 검색
  • Incheon

-- 수정한 사용자 저장 프로시저 호출 (search_name)
-- 매개변수 변경 (@userName -> @userAddr)
-- 매개변수에 "Inc"라는 문자열 입력
exec search_name "Inc"

---

사용자 프로시저 삭제하기

프로시저 삭제 구문

DROP PROCEDURE 키워드 뒤에 프로시저명 기재

-- 프로시저 삭제
DROP PROCEDURE 프로시저명

---

삭제된 프로시저 호출

-- search_name 프로시저 삭제
DROP PROCEDURE search_name

-- 프로시저에 매개변수 "Inc"의 문자열을 넣고 호출
exec search_name "Inc";

-- Error : 저장 프로시저 'search_name'을(를) 찾을 수 없습니다.

• 저장 프로시저 'search_name'을(를) 찾을 수 없습니다.
  
  • 프로시저가 존재하지 않기 때문에 에러 출력