Solved

stored procedures

Posted on 2000-02-24
4
251 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if form parameter is null redirect 2 53
How to update a dummy recordset with classic ASP 4 64
JSON error 4 50
Need help editing script 3 65
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now