SQLAlchemy: Calling MS SQL Stored Procedure with Output Parameters

 SQLAlchemy is generally a wonderful ORM and database toolkit for Python. Personally, I think it's the best one in the Python landscape. However, there are some things that tend to be a little troublesome.

Some of these are simply due to the difference between a relational database and an object oriented language. Others, however, are due to RDBMS specific quirks.

The problem I encountered recently was how to call a stored procedure in MS SQL from SQLAlchemy. In general, the problem isn't a hard one. One can simply import func and be on their way.

from sqlalchemy import func

sess.query(func.sum(TableObject.column)).first()

However, func is geared towards SELECT based functions. For example, the above would be roughly translated into:

SELECT sum(column) FROM TableObject;

Now, MS SQL has stored procedures that return values in the parameters. For example:

DECLARE @noteidx AS NUMERIC(19,5), @err AS SMALLINT;
exec DYNAMICS.[dbo].[smGetNextNoteIndex] dbid, sessid, @noteidx OUTPUT, @err OUTPUT;

This will perform some internal magic and afterwards, @noteidx will hold the next index, and @err will hold whatever error number was encounterd, if any.

This doesn't translate well into SQLAlchemy's idiom of SQL function calling. Though I don't believe it's all SQLAlchemy's fault. It appears that there is  some  support for out parameters in Oracle via SQLAlchemy. Additionally, it appears that PyODBC is lacking  support for out parameters.

However, not willing to roll over and die, there is a solution to be had.

Since SQLAlchemy contains sufficiently quantities of awesome, the solution is only slightly hackish. SQLAlchemy easily allows for the running of straight SQL using the text function. The solution to the above problem:

def get_next_note_index(s=None):
    """ Returns the next note index to use.
    
    Calls the `smGetNextNoteIndex` stored procedure to handle the query
    and increment of the company master NOTEINDX

    If a session object is passed, it will use the one specified.  Otherwise
    it will call get_session() to get a session to use.
    """
    txt = """
        SET NOCOUNT ON;
        DECLARE @db AS CHAR(5), @id AS SMALLINT, @noteidx AS NUMERIC(19,5), 
                @err AS INT;
        SELECT @db=CMPANYID 
          FROM DYNAMICS.[dbo].[SY01500]
         WHERE INTERID = DB_Name();
        SELECT @id=@@SPID;
        EXEC DYNAMICS.[dbo].[smGetNextNoteIndex] @db, @id, @noteidx OUTPUT,
                                                 @err OUTPUT;
        SELECT @noteidx, @err;
        SET NOCOUNT OFF;"""[1:]
    s = s and s or get_session()
    r = s.execute(text(txt)).fetchall()
    s.commit()
    return r[0][0]

The few special things to note about the above are:

• Since there is no direct support for output parameters, one needs to SELECT the values to get them via SQLAlchemy
• Due to all sorts of PyODBC/FreeTDS/*nix/SQLAlchemy  nastiness one needs the SET NOCOUNT ON to keep SQLAlchemy from barfing.

In the end, however, it works nicely. Thank you SQLAlchemy. Also, thank you  MichaelJ2 for the TSQL code to translate.