sp_executesql 사용법 및 매개변수

sp_executesql은 매개변수를 포함하는 쿼리를 효율적으로 실행할 수 있는 명령어로 Transact-SQL 문이나 저장 프로시저에서 주로 사용됩니다.

sp_executesql 특징

• sp_executesql은 동적 쿼리 실행 시 매우 효율적이며 매개변수를 사용할 수 있습니다.
• 실행되는 query 문장은 sp_executesql 문이 실행될 때까지 미리 컴파일되지 않고, sp_executesql로 호출할 때 query 문장은 별도로 실행 계획으로 컴파일하고 실행됩니다.
• 즉, Transact-SQL 문을 여러 번 실행할 때, 실행 계획은 미리 컴파일되어 재사용될 수 있습니다.
• 매개변수에는 유니코드 문자열이 포함되어서 다국어 데이터를 처리할 수 있습니다.
• sp_executesql은 EXECUTE 개선 사항을 나왔고, 보안이 더 강력하며 컴파일 부하가 적어서 요즘은 주로 sp_executesql 추천합니다.

sp_executesql 사용법

동적으로 빌드된 문자열 실행.

• 동적인 SQL을 변수에 넣은 후 sp_executesql에 매개변수를 선언해서 실행하는 과정입니다.
• sp_executesql 실행 흐름도입니다.

sp_executesql 실행되는 흐름도.

DROP TABLE IF EXISTS InfoForSp_executesql;
CREATE TABLE InfoForSp_executesql (
    UserId int,
    UserNm varchar(255),
    UserAge  int ,
	InOutType varchar(10)
);

CREATE PROCEDURE PROCEDURE_FOR_Sp_executesql 
    @ParamUserId    INT
   ,@ParamUserNm    VARCHAR(100)
   ,@ParamUserAge   INT 
 
AS  
DECLARE @InsertString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);  
  
SET @InsertString = ' INSERT INTO  dbo.InfoForSp_executesql ' 
                    + ' ( UserId,UserNm,UserAge, InOutType  ) ';
 						 
IF(@ParamUserAge > 20 )  
   BEGIN  
      SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, @StmtParamUserAge,'+  '''YES''' + ');' 
   END 
ELSE 
   BEGIN
     SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, @StmtParamUserAge,'+  '''NO''' + ');'  
   END                

SET @ParmDefinition =  N'@StmtParamUserId    INT,
                         @StmtParamUserNm    VARCHAR(100),
                         @StmtParamUserAge   INT 
';
 
EXEC sp_executesql @InsertString,  
@ParmDefinition,  
@ParamUserId, 
@ParamUserNm, 
@ParamUserAge   
  
--  EXECUTE  
EXECUTE PROCEDURE_FOR_Sp_executesql @ParamUserId = '200', 
@ParamUserNm = 'mikle', 
@ParamUserAge = 10 ; 					 
--  EXECUTE
EXECUTE PROCEDURE_FOR_Sp_executesql @ParamUserId = '300', 
@ParamUserNm = 'Lee', 
@ParamUserAge = 30 ;
	
SELECT *  
FROM dbo.InfoForSp_executesql;

Transact-SQL 문자열과 별도로 매개 변수 값 설정.

• Transact-SQL에서 sp_executesql 사용하는 예제입니다.

INSERT INTO dbo.InfoForSp_executesql(UserId,UserNm,UserAge,InOutType) VALUES 
(272, N'Ken',35,'')
,(273, N'Brian',70,'')
,(274, N'Stephen',30,'')
,(275, N'Michael',53,'')
,(276, N'Linda',34,'');
 
-- Transact-SQL 문자열과 별도로 매개 변수 값 설정.

DECLARE @IntVariable INT;  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
  
SET @SQLString =  
     N' SELECT UserId,UserNm,UserAge
         FROM InfoForSp_executesql   
         WHERE UserId = @UserId';  
SET @ParmDefinition = N'@UserId INT';  

SET @IntVariable = 272;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @UserId = @IntVariable;

OUTPUT 매개변수 사용.

• 매개변수는 input만 가능한 것은 아니고 data를 OUTPUT 매개변수로 출력할 수 있습니다.

-- OUTPUT 매개 변수 사용.
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
DECLARE @OutPutName NVARCHAR(25);  
DECLARE @IntVariable INT;  

SET @SQLString = N'SELECT @UserNmrOUT = MAX(UserNm)  
    FROM InfoForSp_executesql  
    WHERE UserId = @ParamUserId ';  
SET @ParmDefinition = N'@ParamUserId            INT,  
                        @UserNmrOUT NVARCHAR(25) OUTPUT';  

SET @IntVariable = 300;  
EXECUTE sp_executesql  
     @SQLString  
    ,@ParmDefinition  
    ,@ParamUserId = @IntVariable  
    ,@UserNmrOUT =  @OutPutName OUTPUT;  
-- This SELECT statement returns the value of the OUTPUT parameter.  
SELECT @OutPutName;

sp_executesql 마무리하며

• sp_executesql 나오기 전에는 EXECUTE 문은 보안 문제가 자주 발행했는데 MSSQL에서 sp_executesql 지원함으로써 보완적인 이슈는 없어졌다고 합니다.
• Dynamic SQL 사용할 때는 보통 sp_executesql을 사용합니다.