How do I create this stored procedure in snowflake?

Question

I have this SQL Statements:

CREATE OR REPLACE TABLE TEST_PROCEDURE_STOCKEE_TABLE_KPI AS 

SELECT *
FROM TABLE A

I would like to transform this SQL statement in stored procedure and for the moment i try this :

CREATE OR REPLACE PROCEDURE test_stored_procedure()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN

SELECT *
FROM TABLE A
RETURN 'Success';
END;
$$;

But I have this error message:

SQL compilation error: parse error line 1 at position 3 near '<EOF>'. syntax error line 1 at position 1 unexpected '$'

I would like to store this SQL statement to put this in a task then and call this task every day to create/update this table every day.

Answer 1

Stored procedure:

CREATE OR REPLACE PROCEDURE test_stored_procedure()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE example 
  AS 
  SELECT col1, MAX(col2) AS col2, MAX(col3) AS col3 -- agg function
  FROM Table A
  LEFT JOIN Table B 
    ON A.col1 = B.col2
  WHERE col3 = 'XXXX'
  GROUP BY 1;

  RETURN 'Success';
END;
$$;