Executing stored procedures in sqlalchemy
Question: I am using a raw_connection in sqlalchemy to execute some SQL that runs a
stored procedure.

the stored proc selects a parameter ID at the end. How can I catch this ID?

the python code is:

    
    
    import sqlalchemy as sa
    
    SQLCommand = """
        DECLARE @Msg varbinary(max) = CAST(%s AS varbinary(max) )
        EXECUTE dbo.usp_procedure @Type = 32, @header = %s, @store = @Msg
        """ % (Datacontent, var)
    
    
    Engine = sa.create_engine(connection_string)
    Connection = Engine.raw_connection()
    cursor = Connection.cursor()
    cursor.execute(SQLCommand)
    return_key = list(cursor.fetchall())
    Connection.commit()
    Connection.close()
    

I thought return_key would contain the return code from usp_procedure but it
errors out and I get:

    
    
    No results.  Previous SQL was not a query.
    

The procedure has as a final step:

    
    
    SELECT @ParamID
    

I want to be able to read this code back in my python code


Answer: I don't have `sql-server`and tested this only for `oracle`, however this is
too long for a comment.

Created this simple stored procedure,

    
    
    CREATE OR REPLACE PROCEDURE PROCEDURE1(inp IN VARCHAR2, outp OUT VARCHAR2) AS 
    BEGIN
      IF (inp = 'true') THEN
        outp := '1';
        RETURN;
      END IF;
      IF (inp = 'false') THEN
        outp := '0';
        RETURN;
      END IF;
      outp := NULL;
      RETURN;
    END PROCEDURE1;
    

and tested it with the following code:

    
    
    Connection = c.manager.engine.raw_connection()
    cursor = Connection.cursor()
    result = cursor.callproc("PROCEDURE1", ['true', ''])
    print(result[1])
    result = cursor.callproc("PROCEDURE1", ['false', ''])
    print(result[1])
    

The results are 1 and 0 as expected.

I've been browsing around and I'd expect that `callproc` is available for
`sql-server` e.g.
[here](http://pymssql.org/en/latest/pymssql_examples.html#calling-stored-
procedures) but not honestly I'm not what `sqlalchemy` will be using. Give it
a try, hope it helps.

