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

Question

My question is in close connection to stackoverflow/9915788.

I am using pyodbc to upload data to an external SQL database and for this I use a stored procedure of that database. This procedure returns two things:

a rowset (a single row which contains the text "Success" if the procedure worked fine or the text "Fail" followed by any available reason if it failed)
an integer value (0 for success, -1 for failure)

I managed to read out the integer value using the approach described in the link above, but I would like to read out the rowset as well because I would like to get further information in case of any errors.

Any ideas how to do this? I'm not a SQL expert, but in my understanding a rowset should be a "cursor like" object since it should be possible to loop that row; but how do I get this in a select statement?

This is how I get the 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 I have no idea how to get the row, which should also be available somewhere..

I added a code example of the integer value readout. For the row readout I have no concrete idea and desperately hope you could give me any hint... — joaquinn, Feb 05 '16 at 15:26

score 5 · Accepted Answer · answered Feb 05 '16 at 19:05

5

For the following stored procedure in SQL Server

CREATE PROCEDURE [dbo].[IsItGord] 
    @p1 varchar(50) = 'everybody'
AS
BEGIN
    DECLARE @retval int;
    SET NOCOUNT ON;
    IF @p1 = 'Gord'
    BEGIN
        SET @retval = 0;
        SELECT 'Success' AS response;
    END
    ELSE
    BEGIN
        SET @retval = -1;
        SELECT 'Fail - ' + @p1 + ' is not Gord.' AS 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 I change the .execute() to

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

it prints

[('Fail - Fred is not Gord.', )]
[(-1, )]

answered Feb 05 '16 at 19:05

Gord Thompson

116,920
32
215
418

Thanks Gord for your nice answer! I tried this out but the problem seems to be that the cursor.nextset() gives a boolean False and therefore I cannot call the next Response (if I try a fetchall() after calling nextset() I get "pyodbc.ProgrammingError: No results. Previous SQL was not a query.") I will contact the database admin and ask how the procedure returns the row; it seems to work different than your example, otherwise I would expect it to work this way. – joaquinn Feb 05 '16 at 19:47
When executing SQL Server stored procedures (or possibly any SQL script) from Python, I found that you had to remove any USE { database name } instructions otherwise the "pyodbc.ProgrammingError: No results. Previous SQL was not a query" error occurred. This is likely because pyodbc has already made a connection to a particular database and any attempt to change databases without authentication through a valid connection process should fail. – mrduncle1 Jul 03 '22 at 00:05
@mrduncle1 - https://stackoverflow.com/q/59529459/2144390 – Gord Thompson Jul 03 '22 at 01:12

score 1 · Answer 2 · answered Feb 18 '18 at 17:14

1

always use: set nocount ON, before executing the stored procedure in SQL server.

answered Feb 18 '18 at 17:14

Nish

650
1
8
14

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

2 Answers2