Randy Syring wrote:
> 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).
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 -----
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
