I am consistantly getting an error around my bind variable %s when attempting to call this procedure.
<code>CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y):
snowpark_session.sql("INSERT INTO TABLE_NAME_HERE (X, Y_DATE"
") VALUES (%s, %s)", (x, y_date)).collect()
return 'inserted successfully'
$$
</code>
<code>CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y):
snowpark_session.sql("INSERT INTO TABLE_NAME_HERE (X, Y_DATE"
") VALUES (%s, %s)", (x, y_date)).collect()
return 'inserted successfully'
$$
</code>
CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y):
snowpark_session.sql("INSERT INTO TABLE_NAME_HERE (X, Y_DATE"
") VALUES (%s, %s)", (x, y_date)).collect()
return 'inserted successfully'
$$
This is the error it’s returning. I didn’t have the same issue in Python using sqlalchemy and snowflake.connector so I’m lost why I’m having this issue.
<code>snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7dc70-0002-201f-0001-1f4e02a2d31e: 001003 (42000): SQL compilation error:
syntax error line 1 at position 155 unexpected '%'.
syntax error line 1 at position 159 unexpected '%'.
</code>
<code>snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7dc70-0002-201f-0001-1f4e02a2d31e: 001003 (42000): SQL compilation error:
syntax error line 1 at position 155 unexpected '%'.
syntax error line 1 at position 159 unexpected '%'.
</code>
snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7dc70-0002-201f-0001-1f4e02a2d31e: 001003 (42000): SQL compilation error:
syntax error line 1 at position 155 unexpected '%'.
syntax error line 1 at position 159 unexpected '%'.
This is my call to the proc
<code>CALL insert_proc('999', '2024-10-17');
</code>
<code>CALL insert_proc('999', '2024-10-17');
</code>
CALL insert_proc('999', '2024-10-17');
2
Please find the working stored procedure:
<code>CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y_date):
snowpark_session.sql(f"INSERT INTO TABLE_NAME_HERE VALUES ('{x}', '{y_date}')").collect()
return 'inserted successfully'
$$;
</code>
<code>CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y_date):
snowpark_session.sql(f"INSERT INTO TABLE_NAME_HERE VALUES ('{x}', '{y_date}')").collect()
return 'inserted successfully'
$$;
</code>
CREATE OR REPLACE PROCEDURE insert_proc(
x STRING,
y_date DATE
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'insert_proc'
PACKAGES = ('snowflake-snowpark-python')
EXECUTE AS CALLER
AS
$$
def insert_proc(snowpark_session, x, y_date):
snowpark_session.sql(f"INSERT INTO TABLE_NAME_HERE VALUES ('{x}', '{y_date}')").collect()
return 'inserted successfully'
$$;
2