How do I utilized stored procedure OUTPUT parameters in ASP? 2

[toddman2k]

I am trying to use OUTPUT parameters in my SQL 2000 stored procedures, but can't figure out how to use/assign the parameter back in my ASP pages... Can someone draw me up a quick example?

Thank you very much

 

[JuanitaC]

Could you post your code of how you are executing your stored procedure?

 

[toddman2k]

Actually, I wouldn't mind some advice on the BEST way to do it...

Currently I'm doing something like this:
(which is probably wrong, but heck it works)

For procedures that return a recordset:

rs = conn.execute("exec spMyProcedure Parameter1,2,3,4&quot

I don't know how to call the stored procedure when I want to return an OUTPUT parameter.

 

[link9]

'create a command object
dim comObj
set comObj = server.createObject("ADODB.Command")

'set your properties
comObj.commandType = 4 'adCmdStoredProcedure
comObj.activeConnection = yourConnection
comObj.commandText = "theNameOfYourSPROC"

'assign your input parameters
comObj.parameters("@param1").value = val1
comObj.parameters("@param2").value = val2

'execute your sproc
comObj.execute

'grab your output
output1 = comObj.parameters("@output1").value
output2 = comObj.parameters("@output2").value

where output1 and output2 are declared inside your sproc as OUTPUT


paul

 

[JuanitaC]

I had this problem once too... we found that the return value actually belongs to the connection object, not the recordset.

Try this and see if it works:

rs = conn.execute("exec spMyProcedure Parameter1,2,3,4&quot
rs.close
ret_val=conn("ret_val&quot
Response.write (ret_val)

where conn("ret_val&quot is the name of your return parameter

Do you attach your parameters with
conn.parameters.append conn.addparameter(...)?
If so, make sure you declare the output parameter as output or as inputoutput type.

 

[toddman2k]

Thank you both very much.

Wonderful!