How to pass parameters from databricks to a stored procedure that is in SQL server

Question

I have a stored procedure in SQL server to perform UPSERT operation.

The parameters for the stored procedure are columns of a data frame.

Say I have 3 columns; ColA, ColB, ColC in data frame upsert_df

How do I pass these column names as parameters to the stored procedure?

I am using pyodbc to execute stored procedures from Azure Databricks.

import pyodbc
connect_string = "DRIVER={ODBC Driver 17 for SQL Server};"
connect_string += f"SERVER={jdbchostname},"
connect_string += f"{jdbcport};"
connect_string += f"DATABASE={db};"
connect_string += f"UID={username};"
connect_string += f"PWD={pwd}"

conn = pyodbc.connect(connect_string)
cursor = conn.cursor()  


ColA = upsert_df['ColA']
ColB = upsert_df['ColB']
ColC = upsert_df['ColC']
    
    
proc_call = f'EXEC Proc_Name @ColA=?, @ColB=?, @ColC=?'
    
try:
   cursor.execute(proc_call, (ColA, ColB, ColC))
   conn.commit()
except Exception as e:
   print('Error=', e)   

This code throws below exception. Have I passed the parameters correctly or is there any other issue with the code ?

Error= <class 'pyodbc.Error'> returned a result with an error set

Answer 1

How do I pass these column names as parameters to the stored procedure?

To pass the column name as parameter to stored procedure you can use the following code:

ColA = df.columns[0]
print(ColA)
ColB = df.columns[1]
print(ColB)

It will fetch the column name based on index.

The parameters for the stored procedure are columns of a data frame.

If you want to update the data of your Dataframe to SQL table, you need to iterate on each row of the dataframe and pass those values to the Stored procedure.

Sample Code:

import pyodbc
connect_string = "DRIVER={ODBC Driver 17 for SQL Server};"
connect_string += f"SERVER={jdbchostname},"
connect_string += f"{jdbcport};"
connect_string += f"DATABASE={db};"
connect_string += f"UID={username};"
connect_string += f"PWD={pwd}"

conn = pyodbc.connect(connect_string)
cursor = conn.cursor()  

for row in df.rdd.toLocalIterator():
    ColA = row.Id
    ColB = row.Name    
    proc_call = f'EXEC UpdatePerson @id=?, @name=?'
    try:
        cursor.execute(proc_call, (ColA, ColB))
        conn.commit()
    except Exception as e:
        print('Error=', e)   

It will Iterate on ech row and update that in SQL table.