Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

stored procedures

Posted on 2000-02-24
4
Medium Priority
?
259 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
[X]
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
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 800 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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/…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

730 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