StoredProc

Asp-db offers 3 ways to execute a stored procedure

  1. Stand alone
  2. On-the-fly with parameters
  3. Full parameters - input and output

Basic steps
Define the database connection as described in the Connection Section. There is no need to specify the fields as they do not apply to stored procedures. So, stop when you hit the dbDSN button to generate the connection string.

A special treat
When you are using MS-SQL, you can use the Schema drop down to specify SP to obtain all the user SPs and Views in the system. When you hit the dbDSN button, all the SPs of the initial catalog will be read and loaded into the SP Name box of the StoredProc form. See following illustration -



SP Form

Type 1 - Stand alone StoredProcedure

Screen Dump



 

ASP Source code
Checking the ASPcode generated by the Designer review the following -

<%
Response.buffer = True
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = 1
X.dbDBType = "SQL"
X.dbDSN = "Provider=SQLOLEDB; Initial Catalog=northwind; User id=sa; Data Source=dell500"
X.dbImageDir = "http://YourServerURL/vdata/images/"
X.dbNavigationIcon = "Std"
X.dbStoredProc = "(;,)[Ten Most Expensive Products];1,4"
X.ASPDB
%>

Note: The Group delimiter in this case did not cause a conflict because only the field delimiter is used to separate the SP name and the Command type.

Type 2 - On the fly with parameters StoredProcedure

Screen Dump

ASP Source code
Checking the ASPcode generated by the Designer review the following -

<%
Response.buffer = True
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = 1
X.dbMode = "Grid"
X.dbDBType = "SQL"
X.dbDSN = "Provider=SQLOLEDB; Initial Catalog=pubs; User id=sa; Data Source=dell500"
X.dbStoredProc = "(|,)byroyalty;1,4,50"
X.ASPDB
%>

Type 3 - Full input/output parameters StoredProcedure

The following Oracle function is used to illustrate this type -

create or replace function ExFunct (Num1 in number, Num2 in Number)
return number is temp number;
begin
    temp := Num1 + Num2;
    return temp;
end ExFunct;

Screen Dump

ASP Source code
The code generated by the Designer (in black). The result of a stored procedure is always stored in a session variable of format SV_SP_Unit_VarName. We have to add a few lines of code (in blue) to display the result.


<%
Response.buffer = True
Set X= Server.CreateObject("ASPdb.Y2K")
X.dbUnit = 5
X.dbMode = "Grid"
X.dbDBType = "ORACLE"
X.dbDSN = "Provider=MSDAORA; Data Source=dell; User id=scott; Password=tiger"
X.dbStoredProcCmdParams = "(;~){? = call ExFunct(?,?)}~1~NoRS;Res~139~2~10;Num1~139~1~5~19;Num2~139~1~5~18"
X.ASPdbY2K
response.write("<HR>Input values Num1, Num2 = 19 and 18<BR>")
response.write("Res holds the returned value of function of num1 + Num2 in a session variable SV_SP_Unit_VarName =" & session("SV_SP_5_Res"))

%>