MSSQL Stored Procedures with output (OUT) parameters

[Randy Syring]

I have searched the list and have seen some examples with Oracle and I
have seen some examples with MSSQL using 'exec' but without
parameters. So, I was hoping that someone could give me or point me
to an example of using a MSSQL stored procedure with both input and
output parameters as well as the stored procedure returning a result
set (or two).

Thanks.

[John Hampton]

So, I don't know if you ever found an answer, or what you decided to do,
but I ran into this issue today.

The solution that I came upon was to use sqlalchemy.sql.text to execute
the needed SQL. The real trick was that I had to use a SELECT to get
the value from the output parameters. Additionally, I had to use

SET NOCOUNT ON

in order to get it to not die complaining about using a closed cursor.

Below is the code I used. I hope the formatting doesn't get too screwed
up due to email.

-John

----- Code ------
def get_next_index_id(s=None):
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]
----- End Code -----