Skip to Content
0
Aug 21, 2023 at 10:01 AM

access the value of table variable in a loop while

42 Views Last edit Aug 21, 2023 at 10:05 AM 4 rev

Hello everyone,

I am trying to do a loop while with a variable table. The problem is when I try to access the value select LISTA_ROL_FINAL[:counter] from dummy;

Any idea how I can make it?

Thanks in advance,

Jose Verdu Cano

do
BEGIN
	USING SQLSCRIPT_STRING AS LIBRARY;

	DECLARE LISTA_ROL_FINAL TABLE(RESULT NVARCHAR(1000));
	DECLARE LISTA_ROL_FINAL_2 TABLE(RESULT NVARCHAR(1000));
	
	DECLARE LISTA_ROL VARCHAR(500) := '[TM, AS, AS, AS, AS]';
	DECLARE LISTA_ROL_2 VARCHAR(500) := '[23, 43, 33, 22, 26]';
	
	declare nlista int;
	DECLARE counter INT := 1;
	

	LISTA_ROL := REPLACE(:LISTA_ROL, '[', '');
	LISTA_ROL := REPLACE(:LISTA_ROL, ']', '');
	
	LISTA_ROL_2 := REPLACE(:LISTA_ROL, '[', '');
	LISTA_ROL_2 := REPLACE(:LISTA_ROL, ']', '');
	
	LISTA_ROL_FINAL = LIBRARY:SPLIT_TO_TABLE(:LISTA_ROL,',');
	LISTA_ROL_FINAL_2 = LIBRARY:SPLIT_TO_TABLE(:LISTA_ROL_2,',');

	select count(*) into nlista from :LISTA_ROL_FINAL;


	WHILE :counter <= :nlista DO
		BEGIN
			select LISTA_ROL_FINAL[:counter]  from dummy;
			select LISTA_ROL_FINAL_2[:counter]  from dummy;
				
			counter := :counter + 1;
		END;
	END WHILE;
	
END;