I’m trying to create a store procedure in snowflake where i need to perform a statement to drop some tables in my warehouse. But i’m getting the same error: “The SQL query doesn’t contain any SELECT statement”
<code>`USE SCHEMA "PUBLIC";
CREATE OR REPLACE PROCEDURE DROP_TABLE()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
exec_sql VARCHAR;
BEGIN
SELECT LISTAGG('DROP TABLE IF EXISTS "' || SF_DATABASE || '"."' || SF_SCHEMA || '"."' || SF_TABLE || '";')
WITHIN GROUP (ORDER BY SF_DATABASE, SF_SCHEMA, SF_TABLE)
INTO exec_sql
FROM WAREHOUSE.PUBLIC.DATASETS;
EXECUTE IMMEDIATE exec_sql;
RETURN exec_sql;
END;
$$;
CALL DROP_TABLE();`
</code>
<code>`USE SCHEMA "PUBLIC";
CREATE OR REPLACE PROCEDURE DROP_TABLE()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
exec_sql VARCHAR;
BEGIN
SELECT LISTAGG('DROP TABLE IF EXISTS "' || SF_DATABASE || '"."' || SF_SCHEMA || '"."' || SF_TABLE || '";')
WITHIN GROUP (ORDER BY SF_DATABASE, SF_SCHEMA, SF_TABLE)
INTO exec_sql
FROM WAREHOUSE.PUBLIC.DATASETS;
EXECUTE IMMEDIATE exec_sql;
RETURN exec_sql;
END;
$$;
CALL DROP_TABLE();`
</code>
`USE SCHEMA "PUBLIC";
CREATE OR REPLACE PROCEDURE DROP_TABLE()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
exec_sql VARCHAR;
BEGIN
SELECT LISTAGG('DROP TABLE IF EXISTS "' || SF_DATABASE || '"."' || SF_SCHEMA || '"."' || SF_TABLE || '";')
WITHIN GROUP (ORDER BY SF_DATABASE, SF_SCHEMA, SF_TABLE)
INTO exec_sql
FROM WAREHOUSE.PUBLIC.DATASETS;
EXECUTE IMMEDIATE exec_sql;
RETURN exec_sql;
END;
$$;
CALL DROP_TABLE();`
New contributor
Pedro Caldeira is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.