Retrieve output parameter(s) from a SQL Server stored procedure

Question

Trying to run a MS SQL stored procedure that has an output parameter. I have followed documentation on how to do this, but when I run my code I get this error: SystemError: <class 'pyodbc.Error'> returned a result with an error set. Here is my code:

my_stored_procedure

CREATE PROCEDURE [dbo].[my_stored_procedure]
      @IN1          INT
      @IN2          INT
    , @OUT          INT OUTPUT
AS
BEGIN
    SET @OUT = @IN + 1  
END

myclass.py

  z = sqlalchemy.sql.expression.outparam("ret_%d" % 0, type_=int)
  x = 1
  y = 2
  exec = self.context.\
      execute(text(f"EXEC my_stored_procedure :x, :y, :z OUTPUT"), {"x": x, "y": y, "z": z})
  result = exec.fetchall()

context.py

   def execute(self, statement, args=None):     
        if not args:
            return self.session.execute(statement)
        else:
            return self.session.execute(statement, args)

Any suggestions or can anyone see what I am doing wrong?

score 0 · Accepted Answer · answered Nov 23 '22 at 22:55

.outparam() was added way back in SQLAlchemy 0.4 to address a specific requirement when working with Oracle and is only really used by that dialect. As mentioned in the current SQLAlchemy documentation here, working with stored procedures is one of the more database/dialect-specific tasks because of the significant variations in the way the different DBAPI layers deal with them.

For SQL Server, the pyodbc Wiki explains how to do it here. The good news is that if the stored procedure does not return result sets in addition to the output/return values then you don't need to resort to using a raw DBAPI connection.

For your (corrected) stored procedure

CREATE PROCEDURE [dbo].[my_stored_procedure]
      @IN          INT
    , @OUT         INT OUTPUT
AS
BEGIN
    SET @OUT = @IN + 1  
END

you can use this:

import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")

sql = """\
SET NOCOUNT ON;
DECLARE @out_param int;
EXEC dbo.my_stored_procedure @IN = :in_value, @OUT = @out_param OUTPUT;
SELECT @out_param AS the_output;
"""
with engine.begin() as conn:
    result = conn.execute(sa.text(sql), {"in_value": 1}).scalar()
    print(result)  # 2

Retrieve output parameter(s) from a SQL Server stored procedure

1 Answers1