How to get return value of SQL Server stored procedure using pyodbc?

Question

My team uses a python-based wiki server that calls stored procedures in a SQL Server database. Ideally, we would like to return the integer values ​​(1,0, -1) from the stored procedure to show the main results.

According to a 2008 post in Google groups , the return values ​​are not supported by pyodbc, so the SELECT result is an alternative as well and check it out. Is this still true? Is there a (supported and documented) programmatic way of checking the return value from SQL stored procedures? (If so, add the current link or example.)

Answer 1

Here is some relevant information: http://code.google.com/p/pyodbc/wiki/StoredProcedures

It seems that return values ​​are still not supported, so you need to use SELECT .

Answer 2

I use this:

 def CallStoredProc(conn, procName, *args): sql = """DECLARE @ret int EXEC @ret = %s %s SELECT @ret""" % (procName, ','.join(['?'] * len(args))) return int(conn.execute(sql, args).fetchone()[0]) 

It works only on SQL Server (or maybe Sybase), but it is a decent solution.

Answer 3

I added the following to make it work in my application:

 def CallStoredProc(conn, procName, *args): sql = """SET NOCOUNT ON; DECLARE @ret int EXEC @ret = %s %s SELECT @ret""" % (procName, ','.join(['?'] * len(args))) return int(conn.execute(sql, args).fetchone()[0])