Calling stored procedures which has output parameters

[Timbo]

A few months ago I worked out how to call sql server stored procedures from a web page (with some help). Now I need to call a stored procedure which has some output parameters ie it passes back to the calling software various values. Does anyone know how to specify the parameters in the command line as output parameters? And also how do you get access to the values of these parameters once the stored procedure has run?

[Claire Shuttleworth]

Re: Calling stored procedures which has output parameters

You say you figured out how to do the call?
Would you mind helping me on that?

I need to call a microsoft Sql server stored procedure sending parameters.
I would like to do it as a user defined function.

Is this possible?

thanks!

[Timbo]

Re: Calling stored procedures which has output parameters

Hi, below is the code lifted straight from some Xbasic. If it's not clear just ask some more questions.
Tim

� I use a few session variables to carry data from one page to another.

dim session.u_asc_num as N
dim session.u_sql_sp as C �I use this one to hold the text that I will pass to SQL to execute the procedure
dim session.u_db_result as C
dim session.my_session as N
dim session.u_cs as C �this variable holds the details about my SQL connection string

dim t as SQL::Connection

if .not. t.Open(session.u_cs)
carry out error reporting
end
end if

dim rs as SQL::ResultSet

� build the text of the sql script required to execute the procedure
session.u_sql_sp = "EXEC ASSIGN_AST " + session.u_asc_num + "," + session.my_session

' Execute stored procedure

t.Execute(session.u_sql_sp)
session.u_db_result = t.CallResult.Text
rs = t.ResultSet

� go to next page
response.redirect("aad_C_fdbk_a.a5w")
end

[lehua768]

Re: Calling stored procedures which has output parameters

A few months ago I worked out how to call sql server stored procedures from a web page (with some help). Now I need to call a stored procedure which has some output parameters ie it passes back to the calling software various values. Does anyone know how to specify the parameters in the command line as output parameters? And also how do you get access to the values of these parameters once the stored procedure has run? I use this one to hold the text that I will pass to SQL to execute the procedure.



__________________
Chanel Bags
Gucci Bags
Replica Handbags

[cmkivio]

Re: Calling stored procedures which has output parameters

I have exactly the same problem. I can execute the stored procedure as below in interactive but I can't figure out how to retrieve the returned value:

dim conn as sql::connection

?conn.open("::Name::modiloConnection")
= .T.

dim args as sql::arguments

args.Clear()

?args.set("TenantId",1)
= .T.

?args.set("SequenceId",2)
= .T.

?args.set("NextNumber",99999999,sql::ArgumentUsage::InputOutputArgument)
= .T.

?conn.Execute("exec NewSequenceNumber :TenantId, :SequenceId, :NextNumber",args)
= .T.

?args.Find("NextNumber")
= Data = 99999999
IsNull = .F.
Name = "nextnumber"
Usage = 2
XML = <SQLArgument>
<Name>nextnumber</Name>
<Data Type="N">99999999</Data> <------ NO CHANGE
<IsNull Type="L">0</IsNull>
<Usage>InputOutput</Usage>
</SQLArgument>

/Charles