반응형
Dynamic SQL은 전달된 입력 매개변수를 기반으로 런타임에 SQL 문이 만들어지고 실행되는 매우 유용하게 사용하는 SQL 문입니다.
Dynamic SQL 특징
- Dynamic SQL은 SQL 쿼리를 조건에 따라서 문자열로 만들어 런타임에 동적으로 실행하는 프로그램 방법입니다.
- 프로그램에서 요청하는 변수의 값을 이용하여 Query 문을 동적으로 작성할 수 있습니다.
- Dynamic SQL은 Query를 단순화시킬 수 있고 개발 소스의 양을 줄일 수 있습니다.
- Dynamic SQL 문자열은 EXEC, EXECUTE, sp_executesql을 이용하여 실행합니다.
Dynamic SQL 사용 방법
DML 문에 대한 Dynamic SQL 사용법.
- 문자열 변수에 DML 문을 만든 후 Dynamic SQL 실행합니다.
DROP TABLE IF EXISTS InfoForDynamicSql;
CREATE TABLE InfoForDynamicSql (
UserId int,
UserNm varchar(255) NOT NULL,
UserAge int NULL,
UserAddress varchar(500) NULL
);
INSERT INTO dbo.InfoForDynamicSql(UserId,UserNm)
VALUES (272, N'Ken')
,(273, N'Brian')
,(274, N'Stephen')
,(275, N'Michael')
,(276, N'Linda');
--DML 문에 대한 Dynamic SQL
DECLARE @dynamicSQL nvarchar(max),
@_userId nvarchar(200) ,
@_userAddress nvarchar(200)
-- assign values
set @_userId = CAST(272 as nvarchar(200));
set @_userAddress = CAST('New York in USA ' as nvarchar(200));
-- build dynamic upate statement
SET @dynamicSQL = ' update InfoForDynamicSql SET UserAddress = '''
+ @_userAddress
+ ''' WHERE UserId = '
+ @_userId ;
print(@dynamicSQL);
--execute dynamic statement
execute(@dynamicSQL);
저장 프로시저의 Dynamic SQL 사용법.
- PROCEDURE에서 문자열 변수에 DML 문을 만든 후 실행합니다. 단, 파라미터 변수를 외부에서 받아서 처리해 보세요.
--저장 프로시저의 Dynamic SQL
CREATE PROCEDURE ProcedureFordynamicSQL(
@userId int,
@userAddress nvarchar(200)
)
AS
BEGIN
DECLARE @dynamicSQL nvarchar(max),
@_userId nvarchar(200) ,
@_userAddress nvarchar(200)
-- assign values
set @_userId = CAST(@userId as nvarchar(200));
set @_userAddress = CAST(@userAddress as nvarchar(200));
SET @dynamicSQL = ' update InfoForDynamicSql SET UserAddress = '''
+ @_userAddress + ''' WHERE UserId = ' + @_userId ;
print(@dynamicSQL);
--execute dynamic statement
execute(@dynamicSQL)
END;
EXECUTE ProcedureFordynamicSQL 273,'seoul the korea';
DDL 문에 대한 Dynamic SQL 사용법.
- Dynamic SQL 이용하여 테이블을 삭제하고 생성하는 DDL 문을 실행합니다.
--DDL 문에 대한 Dynamic SQL SQL
DECLARE @dynamicSQL nvarchar(max),
@tablename nvarchar(50),
@columnsInfo varchar(200);
SET @tablename = 'dbo.InfoForDynamicSql'
SET @columnsInfo = '(
UserId int,
UserNm varchar(255) NOT NULL,
UserAge int NULL,
UserAddress varchar(500) NULL
) ';
SET @dynamicSQL = N' DROP TABLE IF EXISTS ' + @tablename + ' ; '
SET @dynamicSQL += N' CREATE TABLE ' + @tablename + @columnsInfo + ' ; '
EXECUTE(@dynamicSQL);
PROCEDURE에서 sp_executesql을 사용하여 Dynamic SQL 사용법.
- Dynamic SQL을 PROCEDURE에 만들고 실행은 sp_executesql을 이용하세요.
--sp_executesql을 사용하여 Dynamic SQL 실행
ALTER PROCEDURE PRO_dynamicSqlForSp_executesql
@ParamUserId INT,
@ParamUserNm VARCHAR(100),
@ParamUserAge INT,
@ParamAddress varchar(500)
AS
DECLARE @dynamicSQL NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
SET @dynamicSQL = ' INSERT INTO dbo.InfoForDynamicSql '
+ ' ( UserId,UserNm,UserAge, UserAddress ) ';
SET @dynamicSQL += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, @StmtParamUserAge,@StmtParamAddress);'
SET @ParmDefinition = N'@StmtParamUserId INT,
@StmtParamUserNm VARCHAR(100),
@StmtParamUserAge INT,
@StmtParamAddress VARCHAR(500)
';
EXECUTE sp_executesql @dynamicSQL, @ParmDefinition,
@ParamUserId,
@ParamUserNm,
@ParamUserAge,
@ParamAddress
-- EXECUTE
EXECUTE PRO_dynamicSqlForSp_executesql
@ParamUserId = '200',
@ParamUserNm = 'mikle',
@ParamUserAge = 10,
@ParamAddress = 'La'
;
sp_executesql의 OUTPUT 매개변수 이용하여 Dynamic SQL 실행.
- Dynamic SQL의 OUTPUT을 sp_executesql을 이용하여 실행합니다.
-- OUTPUT 매개 변수 사용.
DECLARE @dynamicSQL NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @OutPutUserName NVARCHAR(25);
DECLARE @IntVariable INT;
SET @dynamicSQL = N'SELECT @UserNmOUT = MAX(UserNm)
FROM InfoForDynamicSql
WHERE UserId = @ParamUserId ';
SET @ParmDefinition = N'@ParamUserId INT,
@UserNmOUT NVARCHAR(25) OUTPUT
';
EXECUTE sp_executesql
@dynamicSQL
,@ParmDefinition
,@ParamUserId = 200
,@UserNmOUT = @OutPutUserName OUTPUT;
-- SELECT Data.
SELECT @OutPutUserName AS OutPutUserName;
Dynamic SQL 장점, 단점은?
Dynamic SQL 사용의 장점.
- Dynamic SQL은 유용하게 Query를 작성할 수 있습니다.
- Dynamic SQL은 패턴이 있는 Query를 작성할 때 간단하게 할 수 있습니다.
Dynamic SQL 사용의 단점.
- 에러 발생 시 디버깅이 어렵습니다.
- 정적 SQL과 비교해서 소스 파악이 어렵습니다.
- 문자열을 연결해서 사용할 때 보안 문제를 일으킬 수 있습니다.
- MSSQL 서버는 Dynamic SQL을 실행할 때마다 실행 계획을 생성해야 하므로 정적 SQL과 비교해 성능 저하가 있을 수 있습니다.
반응형
