how to obtain return value of a stored procedure in pyodbc ?

2,520 views
Skip to first unread message

Anthony Kong

unread,
Mar 27, 2008, 1:01:40 AM3/27/08
to sqlalchemy
Hi, all

Sorry, it may not be the most relevant groups to ask this question,
but hopefully you may give me some pointer (except "use google". I
have googled with a several combination of keywords but yield nothing
meaningful... :-) )

I have a stored procedure in a MSSQL server. It takes two parameters
and return a value. No resultset/rows are returned.

I am using python2.4, sqlchemy 0.4.3 and pyodbc-2.0.55

I tried for example,

>>> cursor.execute("{CALL some_sp (?, ?)}","20071212", "value1")
-1


But this sp is supposed to return a positive number.

How can I obtain the return value of this sp?

Cheers, Anthony

Rick Morrison

unread,
Mar 27, 2008, 1:15:40 AM3/27/08
to sqlal...@googlegroups.com
Hi Anthony,

Returning values from stored procedures with pyodbc is unfortunately not supported just yet.

If you have access to the stored procedure code, you can perhaps modify it (or wrap it in another procedure) that returns a value via select instead of return. In other words, make the last line of your procedure read:

   SELECT returnvalue

Returning rows from a stored procedure IS supported, and you can fetch the value that way.

Rick

Anthony Kong

unread,
Mar 27, 2008, 1:38:46 AM3/27/08
to sqlalchemy
Thanks Rick! Will follow your advise.

Is it a limitation of the DB API or just a unimplemented feature of
pyodbc?

Cheers, Anthony

Rick Morrison

unread,
Mar 27, 2008, 1:52:07 AM3/27/08
to sqlal...@googlegroups.com
It's primarily pyodbc; the DB-API cursor.callproc() method is not supported.

Anthony Kong

unread,
Mar 27, 2008, 8:02:11 PM3/27/08
to sqlalchemy
Many thanks!

Cheers, Anthony
Reply all
Reply to author
Forward
0 new messages