반응형

동적쿼리

- 코드 실행 시점에 SQL 쿼리문이 동적으로 구성되고 실행되는 쿼리

- Stored Procedure를 생성할 때 필요에 따라 동적 쿼리로 작성

- 동적 쿼리 실행 SQL 문자열에 값이 바뀌는 변수를 넣어 쿼리문을 작성하게 되면 SP가 새로 캐싱되어 재사용성을 떨어트림


정적쿼리

- 변수에 문자열을 대입하여 쿼리문을 작성하는 것이 아닌 일반적으로 작성된 SQL 쿼리

- 정적 쿼리로 작성된 Stored Procedure는 새로 캐싱되지 않아 재사용성을 떨어트리지 않는다.


* 위 내용을 요약하자면 작성한 쿼리가 어떠한 상황에서도 변경되지 않는 쿼리가 정적쿼리가 될 것이고 입력값이나 특정 상황에 따라 쿼리문이 변경될 수 있는 쿼리가 동적쿼리이다.


동적쿼리의 실행

- 동적쿼리는 EXEC()함수를 사용하거나 SP_EXECUTESQL을 사용하여 실행한다.




Stored Procedure(저장 프로시저) < 동적쿼리로 작성된 SP 예제가 있으니 참고

반응형

'Database > SQL Server' 카테고리의 다른 글

SQL 실행계획  (0) 2018.11.12
[MSSQL] Table Column 속성 변경  (0) 2018.06.25
Database 내 모든 테이블 정보 확인  (0) 2018.06.14
Stored Procedure(저장 프로시저)  (0) 2018.05.23
MS-SQL SP_EXECUTESQL  (0) 2018.05.23
반응형

MS SQL Stored Procedure


저장 프로시저란?

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


장점 

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

SP 작성 방법 



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



매개 변수
작성자Your name
만든 날짜Today's date
DescriptionReturns employee data
Procedure_nameHumanResources.uspGetEmployeesTest
@Param1@LastName
@Datatype_For_Param1nvarchar(50)
Default_Value_For_Param1NULL
@Param2@FirstName
@Datatype_For_Param2nvarchar(50)
Default_Value_For_Param2NULL


각 매개 변수칸에 입력 값을 넣어주어 실행하면 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


반응형

'Database > SQL Server' 카테고리의 다른 글

SQL 실행계획  (0) 2018.11.12
[MSSQL] Table Column 속성 변경  (0) 2018.06.25
Database 내 모든 테이블 정보 확인  (0) 2018.06.14
동적 쿼리와 정적 쿼리  (0) 2018.05.23
MS-SQL SP_EXECUTESQL  (0) 2018.05.23
반응형

[MS-SQL SP_EXECUTESQL]



@stmt

- 유니코드 문자열이나 SQL쿼리문을 일괄 처리

- @stmt 유니코드 상수 또는 문자열만 가능

- N'문자열'은 가능하지만 '문자열'은 불가능


@params

- @stmt에 포함된 몯느 매개변수의 정의를 포함하는 유니코드 문자열

- @stmt와 동일하게 유니코드 문자열이나 상수만 가능

- 출력 변수일 경우 [OUT|OUTPUT] 명시


@param1...n

- 매개변수 문자열에 정의 된 첫번째 매개변수의 값

- @stmt에 제공되는 모든 매개변수에 대해 제공되는 매개변수 값이 있어야 한다







*참고문서 https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017



반응형

'Database > SQL Server' 카테고리의 다른 글

SQL 실행계획  (0) 2018.11.12
[MSSQL] Table Column 속성 변경  (0) 2018.06.25
Database 내 모든 테이블 정보 확인  (0) 2018.06.14
동적 쿼리와 정적 쿼리  (0) 2018.05.23
Stored Procedure(저장 프로시저)  (0) 2018.05.23

+ Recent posts