본문 바로가기

관리 메뉴

MSSQL 프로시저 파라미터 동적 처리하기(함수 활용) 본문

RDBMS

MSSQL 프로시저 파라미터 동적 처리하기(함수 활용)

SaintsP 2020. 10. 7. 16:59

Stored Procedure를 활용하여 작업을 하다보면 때로는 파라미터를 동적으로 받아서 활용하고 싶을 때가 있습니다.


예를 들어 id List를 파라미터로 받아서 해당하는 각각 id 정보로 select를 하는 프로시저를 만들고 싶을 때가 있는데요, 


다음의 테이블 반환 함수를 활용하면 간단하게 동적 파라미터를 사용할 수 있습니다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE FUNCTION [dbo].[fnSplitString] 
    @string NVARCHAR(MAX), 
    @delimiter VARCHAR(2
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + len(@delimiter) 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END
cs


fnSplitString 함수는 @string을 @delimiter로 split 해서 결과를 테이블로 반환하는 간단함 함수입니다.


프로시저의 파라미터에 '1/2/3/4'나 '1/2/3/4/5/6/7/8/9/10' 등등으로 동적으로 입력 받은 파라미터를 임시 테이블 및 fnSplitString 함수를 활용하여 동적으로 처리할 수 있습니다. (구분자는 임의로 변경하셔도 됩니다.)



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE PROCEDURE [dbo].[SP1] 
(
    @idList varchar(1000)
)
AS
BEGIN
 
    -- 임시 테이블 선언
    declare @temp_log table (seq int identity, id bigint, name varchar(50))
 
    -- 임시 테이블에 동적으로 받은 id 삽입('/' 구분자 활용)
    insert into @temp_log (id) (
        select splitdata from [fnSplitString](@idList, '/')
    )
    
    declare @row int
    declare @row_count int
 
    set @row_count = (select count(seq) from @temp_log)
    set @row = 1
 
    -- 임시 테이블의 열 개수만큼 돌면서 입력된 id로 name을 조회해 다시 시 테이블의 해당하는 name을 업데이트
    while(@row <= @row_count)
 
        begin
            
            set @tmpId = (select top 1 id from @temp_log where seq = @row)
            set @tmpName = (select name from id_name_table where id = @tmpId)
 
            
            update @temp_log set name = @tmpName where id = @tmpId
          
            set @row = @row + 1
            
 
        end
 
     -- 모든 열에 대한 업데이트를 마치면 임시 테이블 select
     select * from @temp_log
 
end
cs



exec [sp1] @idList='1/2/3/4';

exec [sp1] @idList='1/2/3/4/5/6/7/8/9/10';



이렇게 동적으로 입력받은 파라미터를 가지고 해당하는 id의 name 정보를 동적으로 반환하는 Stored Procedure를 만들 수 있습니다.





Comments