8

My team's using a python-based wiki server that calls stored procedures on a SQL Server database. Ideally, we'd like to return integer values (1,0,-1) from the stored procedure to show basic results.

According to a 2008 thread on Google Groups, return values aren't supported by pyodbc, so the alternative is to SELECT the result as a row and check it instead. Is that still the case? Is there a (supported and documented) programmatic way to check the return value from SQL stored procedures? (If so, please add a current reference or example.)

Gabe
  • 84,912
  • 12
  • 139
  • 238
Matthew Glidden
  • 382
  • 2
  • 6
  • 15

4 Answers4

8

I'm using 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 only works on SQL Server (or maybe Sybase), but it's a decent workaround.

Gabe
  • 84,912
  • 12
  • 139
  • 238
3

I added the following to get it to 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])
S McCrohan
  • 6,663
  • 1
  • 30
  • 39
user4839546
  • 31
  • 1
3

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

It sounds like return values are still not supported, so you'll need to use a SELECT.

Colin
  • 846
  • 7
  • 16
0

I used similar solution of Gabe and I needed Named parameters:

def call_stored_procedure(conn, procName, **args):
    query = ""
    for key, value in  args.items():
        query = query+',@'+key+'='+'\"'+str(value)+'\"'
    query = query[ 1:]

    sql = """   SET QUOTED_IDENTIFIER OFF
                SET ANSI_NULLS ON 
                SET NOCOUNT ON
                DECLARE @ret int
                 
                 EXEC @ret = %s %s       

                COMMIT 
                SELECT @ret""" % (procName, query)

    id = conn.execute(sql).fetchone()[0]

call_stored_procedure(conn, "MY_StoredProcedure", **payload)
mvitor
  • 37
  • 8