topic Re: Call a Stored Procedure in Azure Synapse with input and output Params in Data Engineering

Call a Stored Procedure in Azure Synapse with input and output Params

Akshith_Rajesh — Fri, 12 May 2023 17:13:36 GMT

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url, mssql_user, mssql_pass)
connection.prepareCall("EXEC sys.sp_tables").execute()
connection.close()

The above code works fine but however I am unable to capture the output Params from Stored Procedure

Can someone please share the snippet to capture output value

Re: Call a Stored Procedure in Azure Synapse with input and output Params

Ryan_Chynoweth — Fri, 12 May 2023 21:42:16 GMT

I think there is an `executeQuery()` function that also works and returns a resultset. Something like this (I have not tested though):

conn = DriverManager.getConnection(url, username, password);
stmt = conn.createStatement();
sql = "SELECT * FROM mytable";
rs = stmt.executeQuery(sql);

Re: Call a Stored Procedure in Azure Synapse with input and output Params

Akshith_Rajesh — Sat, 13 May 2023 10:15:21 GMT

If I print(rs) I am not receiving the expected result

Re: Call a Stored Procedure in Azure Synapse with input and output Params

Ryan_Chynoweth — Fri, 09 Jun 2023 16:48:48 GMT

Can you provide more details on the output? I am not entirely sure if the print statement would show the value of rs as it may just show the type of the python object since it is not a string variable.

Re: Call a Stored Procedure in Azure Synapse with input and output Params

sivaram_bh — Fri, 03 Nov 2023 11:59:30 GMT

statement="EXEC procedurename imputparametre , ? "

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager

con = driver_manager.getConnection(jdbcUrl, username, pwd)

exec_statement = con.prepareCall(statement)

exec_statement.registerOutParameter(1, spark._sc._gateway.jvm.java.sql.Types.VARCHAR)

df=exec_statement.execute()

# Close connections

result=exec_statement.getString(1)

display(result)

exec_statement.close()

con.close()

Re: Call a Stored Procedure in Azure Synapse with input and output Params

judyy — Thu, 30 May 2024 08:19:42 GMT

This blog helped me with the output of the stored procedure: https://medium.com/@judy3.yang/how-to-run-sql-procedure-in-databricks-notebook-e28023555565