Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

stored procedures

hi...
i am using stored procedures in oracle db from my asp pages.
let's assume the store procedure is called "update_info".
let's also assume this procedure is getting 2 input variable,and
it's output contain an integer betwean 0 and 4.
well...my questain is...what is my syntsx for that?
remember...it is not ordinary procedure to execute.
it procedure that gets 2 parameters and also return parameter.
this is the first time i do it so please be clear and dont complicates
me...

elad
0
eladr
Asked:
eladr
1 Solution
 
eladrAuthor Commented:
Adjusted points to 200
0
 
robbertCommented:
In T-SQL, it was the following. Don't know the syntax for Oracle, sorry.

CREATE PROCEDURE myProc
    @param1 int,
    @param2 int,
    @param3 int OUTPUT -- not being input
AS
SELECT @param3 FROM myTable
RETURN
0
 
FirAlianCommented:
And here's the sample code to use you stored procedure from ASP. Follwoing code assumes to get two inout parameters from html form, and after execution, saves output value in a variable 'temp'. This is juts a sample code, and you might need to edit this code, according to your needs.
=============

Set DataConn = Server.CreateObject("ADODB.Connection")
set DataComm = Server.CreateObject("ADODB.Command")            
DataConn.Open "dsn=db1;uid=sa;pwd="
Set DataComm.ActiveConnection=DataConn
DataComm.CommandType = adCMDStoredProc
DataComm.CommandText="sp_DoAllCheck"
'sp_DoAllCheck is name of Stored Procedure            
      
set myFirstParam = DataComm.CreateParameter("param1", adVarInt, adParamInput, 30)

dataComm.parameters.append myfirstparam
      
set mySecondParam = DataComm.CreateParameter("Param2", adVarInt, adParamInput, 13)

dataComm.parameters.append MySecondParam
      
set myThirdParam = DataComm.CreateParameter("param3", adChar, adParamOutput, 2)

dataComm.parameters.append MyThirdParam
      
DataComm("param1")=Request.FORM("valueOfparam1")

DataComm("param2")=Request.FORM("valueOfparam2")

datacomm.execute
            
temp = datacomm("param3")
0
 
Yury_DelendikCommented:
You may create procedure:

CREATE PROCEDURE TT (I INTEGER, J INTEGER, K OUT INTEGER) AS
BEGIN
  K := MOD(I + J, 5);
END;


and you ASP code:

<html>
<body>
<%
Set DBconn= Server.CreateObject("ADODB.Connection")
DBConn.Open "base", "user", "pwd"

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = DBconn
cmd.CommandText = "TT"
cmd.CommandType = 4 ' cmdStoredProc
cmd.Prepared = True
cmd.Parameters.Refresh
cmd.Parameters("I") = 10
cmd.Parameters("J") = 3
cmd.Execute
Response.Write cmd.Parameters("K")

DBConn.Close
%>
</body></html>

It's really simple. Good luck.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now