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

[Akshith_Rajesh]

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

[Ryan_Chynoweth · Accepted solution]

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);

[Akshith_Rajesh]

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

[Ryan_Chynoweth]

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.

[sivaram_bh]

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()

[judyy]

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