Problems to call procedure and return of an outputvar
Hello
I have trouble capturing the value from a stored procedures output variable from an MS SQL Database.
In the database querywindow of MS SQL manager, the procedure is called as:
declare @return As varchar (100)
Execute TheProcedure @return. @return then contains string of content.
From HC I call the procedure:
set RetValue = ""
set Inparm = "@return"
set Outparm = ## class (% ListOfDataTypes).% New ()
SET SQLQuery = "exec TheProcedure ?"
(have also tried
"{call TheProcedure (?)}"
SET tSC = ..Adapter.ExecuteProcedure (, .Outparm, SQLQuery, "o *", Inparm)
set RetValue = outparm.GetAt(1)
This constantly creates errors. I have tried different variants but nothing works.
Outparm is always null or empty.
Greatful for help with the right syntax!
Sincerely, Michael
Comments
Good morning Michael,
I haven't worked a lot with stored procedures but when I have worked with them, I did it like this.
TestSQLProc()New stmt,status,rSet,SQLQuery
SET SQLQuery = "SELECT PackageName.ClassName_ProcedureName('parameter')"
Set stmt = ##class(%SQL.Statement).%New() Set status = stmt.%Prepare(SQLQuery)
If status'=1
{ Set return="%Prepare failed:" Do $System.Status.DisplayError(status) Quit }
Set rSet = stmt.%Execute()While rSet.%Next() { U 0 W rSet."ColumnName" }
QI think that your problem is that you're trying to get information using Call instead of Select.
I hope it helps you.
My best,
Hi and thanks for your reply!
Since this is not a stored procedure in Iris database but in an MS SQL server, syntax does not work
"SELECT PackageName.ClassName_ProcedureName ('parameter')"
Normal call is Execute procedure name.
For example, when I call a procedure that does not use an output parameter, I use
"..Adapter.ExecuteQuery (.tResult, tQuery) Quit: $$$ ISERR (tStatus)"
However, because this procedure fills an output parameter, this call does not work. It then returns "Error! Function must return a value". So i suppose i need to use ExecuteProcedure
Sincerely, Michael
***Solved***
s theResponse = ##class(%ListOfObjects).%New()
s SQLQuery = "{call TheProcedure }"
s tSC = ..Adapter.ExecuteProcedure(.theResponse , ,SQLQuery)
s tSnapshot = theResponse.GetAt(1)