SQL Server stored prcedures with output parameters

[Steve Holden]

Tim Golden wrote:

> Well, this is not much more than "me, too" kind of 
> information, but just in case...
> 
> The Object Craft MSSQL module which I use every day
> for SQL stuff doesn't have a working callproc method,
> something which has never bothered me before, but which
> would have been useful now.
> 
Yes. A lot of DB API modules have just punted on callproc, which is 
damned annoying when you need it :-(

I haven't used the MSSQL module, but adodbapi seems like the most 
complete interface.

> The pymssql module -- which seems a bit underdeveloped --
> doesn't have a callproc at all.
> 
I have tried that briefly, but saw no compelling reason to adopt it.

> What I do to get things out of a stored proc is to
> select out at the end and let Python pick it up as
> a result set. Just in case you're unfamiliar with the
> technique (and apologies if I'm teaching my granny...):
> 
> <sql>
> CREATE PROCEDURE test @i INT AS
>   SELECT @i
> GO
> </sql>
> 
> <python>
> # Uses Object Craft module, but should work 
> #  with other interface modules.
> import MSSQL 
> db = MSSQL.connect ("VODEV1", "", "", "EVODEV")
> q = db.cursor ()
> q.execute ("EXECUTE test 1")
> print q.fetchone ()
> </python>
> 
> Not much help, I know, but I'm afraid I have about 3 hours less
> experience in ADO than you.
> 
No apologies necessary. Unfortunately these are somebody else's stored 
procedures, and the suggestion that they change them to accommodate the 
weaknesses of the Python interface wouldn't be well received. These 
procedures have output parameters, and that's where I'll have to get the 
results from.

I'm probably going to have to work around it for now by duplicating the 
SQL, but that has bad maintenance problems long term.

regards
  Steve
-- 
http://www.holdenweb.com
http://pydish.holdenweb.com
Holden Web LLC +1 800 494 3119