MSSQL 프로시저 파라미터 동적 처리하기(함수 활용) 본문
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