Skip to Content
0
Jan 31, 2023 at 11:04 AM

Dynamically writing functions/procedures into text file and then executing

88 Views Last edit Feb 03, 2023 at 12:57 PM 2 rev

Hi,

I am trying to create functions which uses IF ELSE loops depending on some user data(which can vary) using dynamic sql for automating some process. Currently, I am plainly using string(NVARCHAR(5000)) and concatenating in a FOR LOOP for writing the syntax of functions. However, at some point, the final string reaches the maximum chars value.

Example: in this code snippet, the CREATE_LEVEL_IF_BLOCK is exceeding the max range of NVARCHAR(500) sometimes.

To resolve this issue, I am planning to write the string variables (containing the syntax of the functions) into a file and then execute the file. Does anyone knows what is the syntax of writing into a file and executing it or any better way to solve this issue ?

FYI: In any case, static function creation is not an option currently.

	FOR i in 1..hierarchyLevelCount DO
		DECLARE levels NVARCHAR(10);
		DECLARE RANGES TABLE(RESULT NVARCHAR(50));
		DECLARE totalRows INTEGER;
		DECLARE cnt INTEGER = 0;
		DECLARE CURSOR RANGES_CURSOR FOR SELECT "RESULT" FROM :RANGES;
		
		IF i = 1 THEN
			CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK ||
			'IF (level = 1) THEN ';
		ELSE
			CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK ||
			'ELSEIF (level = 2) THEN ';
		END IF;
		
		SELECT "WORDS" INTO levels FROM DIGITS_TO_WORDS WHERE "DIGITS" = i;
		GET_HIERARCHIES_FROM_JSON_DQUERY :=
		'SELECT JSON_QUERY("Parameters", ''$.Hierarchy_levels.' || levels || ''') FROM CONFIG'
		'	WHERE "Column" = ''' || columnName || ''';';
		EXECUTE IMMEDIATE GET_HIERARCHIES_FROM_JSON_DQUERY INTO hierarchies;
		
		SELECT TRIM ('[]' FROM hierarchies) "trim both" INTO hierarchies FROM DUMMY;
		
		RANGES = LIB2:SPLIT_TO_TABLE(hierarchies, ',');
		SELECT COUNT(*) INTO totalRows FROM :RANGES;
		FOR C_ROW AS RANGES_CURSOR DO
			DECLARE eachRange NVARCHAR(50);
			DECLARE lowerRange, upperRange INT;

			cnt := cnt + 1;
			SELECT TRIM ('"' FROM C_ROW."RESULT") "trim both" INTO eachRange FROM DUMMY;
			
			IF cnt = totalRows THEN
				CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
				'ELSE outValue := ''' || eachRange || '''; END IF; ';
			ELSE
				(lowerRange, upperRange) = LIB2:SPLIT(eachRange, '-');
				IF cnt = 1 THEN
					CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
					'IF (num >= ' || lowerRange || ' AND num <= ' || upperRange || ') THEN '
					'outValue := ''' || eachRange || '''; ';
				ELSE
					CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
					'ELSEIF (num >= ' || lowerRange || ' AND num <= ' || upperRange || ') THEN '
					'outValue := ''' || eachRange || '''; ';
				END IF;
			END IF;
		END FOR;
		CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK || CREATE_RANGES_IF_BLOCK;
	END FOR;<br>