C# GeneralDictionary -> Sql Parameter 변환

Visual Studio에서 디버깅을 통해 매개변수 값을 확인할 때
조건부를 통해 확인하는데 이때 매개변수가 여러개일 경우
해당 매개변수를 아래 코드를 통해 sql paramter로 쉽게 변환할 수 있다.

------------------------------------------------------------------------
- INFO		  : UP_GeneralDictionary_TO_Parameter
- CREATE BY   : 2022.12.07 / Chun Eun Jung
- DESCRIPTION : C# GeneralDictionary -> Sql Parameter 변환
- SAMPLE      : 
 	exec UP_GeneralDictionary_TO_Parameter	@GeneralDictionary = '+		[0]	{[plant_cd, 001]}	System.Collections.Generic.KeyValuePair<string, object>
+		[1]	{[decommission_reason, SAMPLING]}	System.Collections.Generic.KeyValuePair<string, object>
+		[2]	{[box_barcode, P190900117]}	System.Collections.Generic.KeyValuePair<string, object>
+		[3]	{[out_qty, 5]}	System.Collections.Generic.KeyValuePair<string, object>
+		[4]	{[out_request_emp_cd, ]}	System.Collections.Generic.KeyValuePair<string, object>
+		[5]	{[out_request_dept_cd, ]}	System.Collections.Generic.KeyValuePair<string, object>
+		[6]	{[out_request_remark, ]}	System.Collections.Generic.KeyValuePair<string, object>
+		[7]	{[out_status, REQUEST]}	System.Collections.Generic.KeyValuePair<string, object>
+		[8]	{[update_user_id, admin]}	System.Collections.Generic.KeyValuePair<string, object>'
------------------------------------------------------------------------

create PROCEDURE [dbo].[UP_GeneralDictionary_TO_Parameter] ( @GeneralDictionary 	NVARCHAR(MAX))
AS
BEGIN
	
	create table  #tmp_list	(
			row_num					int identity(1,1)
		, 	parameter				nvarchar(2000)
	)			
	
	create table #tmp_result (
			row_num					int identity(1,1)
		, 	parameter				nvarchar(2000)
		, 	parameter_value			nvarchar(2000)
	)
	 
	declare	@cur_parameter			nvarchar(2000)
	
	declare	@strparam			nvarchar(100)  
		,	@parameter1			int 		  
		,	@gubun_comma		int
		,	@parameter2			int
		, 	@parameter			nvarchar(100)
		,	@parameter_value	nvarchar(100)		

	insert into #tmp_list
	select *
	  from dbo.fun_string_to_table(@GeneralDictionary, char(10))
	 
	declare @data_rec_cnt			int	= 1
		,	@data_tot_cnt			int	= 0
	select	@data_tot_cnt = max(row_num) from #tmp_list

	while (@data_rec_cnt <= @data_tot_cnt )
	begin
		select	@cur_parameter		= parameter
		  from 	#tmp_list
		 where	row_num				= @data_rec_cnt	 		 		

		select 	@strparam			= @cur_parameter
		                        	
		select @parameter1			= charindex( '{', @strparam, 1) + 2
		select @gubun_comma			= charindex( ',', @strparam, 1)
		select @parameter2			= charindex( '}', @strparam, 1)
		                        	
		select @parameter			= '@' + substring(@strparam, @parameter1, @gubun_comma - @parameter1)
		select @parameter_value   	= ' = ''' + substring(@strparam, @gubun_comma+2, @parameter2-@gubun_comma-3) + ''''
			
		insert into #tmp_result (
				parameter				
			,	parameter_value
		) values (
				@parameter				
			,	@parameter_value				
		)		
		 
		set @data_rec_cnt = @data_rec_cnt + 1
	end	
	
	update #tmp_result set parameter = ',' + parameter where row_num > 1

	select * 
	  from 	#tmp_result
	  
	drop table #tmp_list	
	drop table #tmp_result	  
END
go