Skip to Content
0
Aug 08, 2023 at 10:52 AM

Dropping tables whose names are in another table

82 Views Last edit Aug 08, 2023 at 10:56 AM 6 rev

Hi,

I'd like to create a procedure in HANA Cloud to drop several tables whose names are stored in another table.

I tried something like this, but it doesn't work.

I'm not familiar to HANA so maybe this syntax is completely wrong. I'd appreciate very much any help.

CREATE PROCEDURE delete_temp AS BEGIN
	DECLARE table_name NVARCHAR(512);
	DECLARE SQL_statement NVARCHAR(512);
	-- Declare Cursor 
	DECLARE CURSOR curTables FOR SELECT table_name FROM TABLES_TO_DELETE;
	-- Open Cursor
	OPEN curTables;
	FETCH NEXT FROM curTables INTO table_name;
		WHILE @@FETCH_STATUS = 0 DO
				SET SQL_statement = 'DROP TABLE MY_SCHEMA.' + QUOTENAME(table_name);
				EXEC sp_executesql SQL_statement;
				FETCH NEXT FROM curTables INTO table_name;
		END WHILE;
	CLOSE curTables;
	
END;