CODEWITHSUNDEEP
pythonsql-serverpyodbc
printthis
printthis

Reputation: 151

Pyodbc - read output parameter of stored procedure (SQL Server)

I'm trying to get the output parameter of an SQL Server stored procedure using pyodbc. The procedure is located in SQL Server and I can insert data successfully from my program. This is part of the SP:

INSERT INTO Table(a,b,c,d,e,f,g) 
VALUES (@a,@b,@c,@d,@e,@f,@g);
SET @Result = 'Inserted'
RETURN @Result

When I try to read the result variable in my code it shows empty.

Upvotes: 2

Views: 9104

Answers (2)

Ntsikelelo Ndleleni
Ntsikelelo Ndleleni

Reputation: 41

The above answer from Printthis worked and thanks a lot, yes i also added conn.commit() after:

row = cursor.fetchone() 
token=row[0]
conn.commit()

if i put commit before fetchone, i would get an error, sequence error sqlfetch...

Upvotes: 1

printthis
printthis

Reputation: 151

This is the call from python, I used pyodbc:

cursor = self.db.cursor()

sql = """\
DECLARE @out nvarchar(max);
EXEC [dbo].[storedProcedure] @x = ?, @y = ?, @z = ?,@param_out = @out OUTPUT;
SELECT @out AS the_output;
"""

cursor.execute(sql, (x, y, z))
row = cursor.fetchone()
print(row[0])

Then create a stored procedure with an output parameter.

Upvotes: 8

Related Questions