Solved

stored procedures

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

16 Experts available now in Live!

Get 1:1 Help Now