Stored Procedure(저장 프로시저)

MS SQL Stored Procedure

저장 프로시저란?

- SQL Query문들을 하나의 함수처럼 실행하기 위한 Query의 집합

장점 

1. 하나의 요청으로 여러 SQL 쿼리를 실행하여 네트워크 부하 감소
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 함으로 처리 시간 감소
3. 최초 사용한 SP는 캐싱이 되어 재사용이 용이
4. 사용자에게 테이블 접근 권한을 대신 프로지서 접근 권한을 주어 DataBase 보안성 강화
5. 모듈식 프로그래밍 가능

SP 작성 방법 

위와 같이 SSMS(2014) 에서 클릭하면 아래와 같은 파일이 생긴다

매개 변수	값
작성자	Your name
만든 날짜	Today's date
Description	Returns employee data
Procedure_name	HumanResources.uspGetEmployeesTest
@Param1	@LastName
@Datatype_For_Param1	nvarchar(50)
Default_Value_For_Param1	NULL
@Param2	@FirstName
@Datatype_For_Param2	nvarchar(50)
Default_Value_For_Param2	NULL

각 매개 변수칸에 입력 값을 넣어주어 실행하면 SP가 만들어 진다.

---

예제 SP를 실행하기 위한 table 셋팅

CREATE TABLE testTable(

NAME NVARCHAR(50),

AGE INT)

GO

INSERT INTO testTable (NAME,AGE) VALUES ('코드블랙',15)

INSERT INTO testTable (NAME,AGE) VALUES ('홍길동',25)

INSERT INTO testTable (NAME,AGE) VALUES ('김복남',35)

INSERT INTO testTable  (NAME,AGE) VALUES ('이순신',52)

INSERT INTO testTable (NAME,AGE) VALUES ('둘리',26)

SELECT * 

FROM testTable

위 쿼리를 실행하면 테이블을 만들고 만들어진 테이블 내용을 확인할 수 있다.

---

동적쿼리와 sp_executesql을 이용한 입력 변수와 출력변수가 있는 SP 예제

- @input의 입력 값의 유무에 따라 WHERE절을 @Sql String에 추가하여 Sql문이 새로 캐싱 되는 SP

CREATE PROCEDURE TestSP 

-- Add the parameters for the stored procedure here

    @input       NVARCHAR(50) =null,

    @output     INT                 =0   OUT

AS

----------------------------------------------------

--sp_executesql을 실행할 변수 선언

----------------------------------------------------

DECLARE @Sql      NVARCHAR(MAX) = ''

DECLARE @Where  NVARCHAR(MAX) = ''

DECLARE @Param  NVARCHAR(MAX) = ''

----------------------------------------------------

--입력 이름이 들어오면 WHERE절 추가

----------------------------------------------------

IF ISNULL(@input,'') <> '' BEGIN

SET @Where = @Where + N'

    WHERE NAME = @input '

END

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET @Sql = @Sql + N'

    SELECT @output = AGE

    FROM   dbo.testTable '

SET @Sql = @Sql + @Where

SET @Param = N'

    @input  NVARCHAR(50),

    @output INT OUT '

EXEC SP_EXECUTESQL @Sql

                  ,@Param

                  ,@input  = @input

                  ,@output = @output