python - How to get a SQL Server stored procedure returned rowset using pyodbc? -

my question in close connection stackoverflow/9915788.

i using pyodbc upload data external sql database , use stored procedure of database. procedure returns 2 things:

• a rowset (a single row contains text "success" if procedure worked fine or text "fail" followed available reason if failed)
• an integer value (0 success, -1 failure)

i managed read out integer value using approach described in link above, read out rowset because further information in case of errors.

any ideas how this? i'm not sql expert, in understanding rowset should "cursor like" object since should possible loop row; how in select statement?

this how integer value:

def callstoredproc(conn, procname, *args):     sql = """set nocount on;             declare @ret int             exec @ret = %s %s             select @ret""" % (procname, ','.join(['?'] * len(args)))     return conn.execute(sql, args).fetchall() 

but have no idea how row, should available somewhere..

for following stored procedure in sql server

create procedure [dbo].[isitgord]      @p1 varchar(50) = 'everybody' begin     declare @retval int;     set nocount on;     if @p1 = 'gord'     begin         set @retval = 0;         select 'success' response;     end     else     begin         set @retval = -1;         select 'fail - ' + @p1 + ' not gord.' response;     end     return @retval; end 

the following pyodbc code

cnxn = pyodbc.connect(connstr) sql = """\ declare @return_value int; exec    @return_value = [dbo].[isitgord] ?; select  'return value' = @return_value; """ crsr = cnxn.execute(sql, ("gord")) data = crsr.fetchall() print(data) crsr.nextset() data = crsr.fetchall() print(data) 

prints

[('success', )] [(0, )] 

and if change .execute() to

crsr = cnxn.execute(sql, ("fred")) 

it prints

[('fail - fred not gord.', )] [(-1, )]