How to retrieve an output parameter of a stored procedure?

user_14405692 0
Viewed 1225

I'm trying to execute an SQL stored procedure which does some inserts an updates, and I pass a parameter called lcLogSessionId to it. During the execution of the procedure this parameter is set to 1 if there are errors, and remains to 0 if everything worked correctly. In my python script I need to retrieve that value at the end of the execution of the stored procedure.

This is my code:

stored_proc = "Exec [APP].[dataImport] @lcLogSessionId = %s"%(0)
conn = pyodbc.connect(connection_string, autocommit = True)
cursor = conn.cursor()
cursor.execute(stored_proc)

I've tried to fetch the cursor with row = cursor.fetchone(), but i get an error which says "No results. Previous SQL was not a query". Is there a way to get the value of lcLogSessionId?

pyodbc stored-procedures sql python
1 Answers

pyodbc does not currently implement the optional .callproc() method, so a workaround is required to retrieve output parameters and return values. For SQL Server that involves an anonymous code block to EXEC the stored procedure and then SELECT the values, e.g.,

sql = """\
DECLARE @out nvarchar(max);
EXEC [dbo].[test_for_pyodbc] @param_in = ?, @param_out = @out OUTPUT;
SELECT @out AS the_output;
"""
params = ("Burma!", )
crsr.execute(sql, params)
rows = crsr.fetchall()
while rows:
    print(rows)
    if crsr.nextset():
        rows = crsr.fetchall()
    else:
        rows = None

More details at the pyodbc wiki:

https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures#output-parameters-and-return-values

user_21443900
Related