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

[Anthony Kong]

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]

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]

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]

It's primarily pyodbc; the DB-API cursor.callproc() method is not supported.

[Anthony Kong]

Many thanks!

Cheers, Anthony