Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

stored procedures

Posted on 2000-02-24
4
254 Views
Last Modified: 2008-02-01
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
Comment
Question by:eladr
4 Comments
 
LVL 1

Author Comment

by:eladr
ID: 2557781
Adjusted points to 200
0
 
LVL 15

Expert Comment

by:robbert
ID: 2557785
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
 

Expert Comment

by:FirAlian
ID: 2558270
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
 
LVL 10

Accepted Solution

by:
Yury_Delendik earned 200 total points
ID: 2567701
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question