dbStoredProc | dbStoredProcCmdParams

Execute Stored Procedures using on-the-fly or full parameter options. If dbStoredProc is used, dbSQL is not required. Multiple recordsets return is also demonstrated in example #4.

To define output variables, use the Full parameters syntax and intdir=2. The returned variable will be placed in the session variable SV_SP_Unit_strName. See example #6 for an illustration.

Property Syntax

Property Syntax Description
dbStoredProc StoredProcName   On-the-fly [CommandType defaults to 4]
dbStoredProc (;,)StoredProcName,CommandType,Param1,Param2,... On-the-fly with parameters
dbStoredProcCmdParams (;,)StoredProcName,CommandType,NoRS; StrName1,inttype1,intdir1,intsize1, strvalue1; .. repeat Full parameters

Parameters

Parameter Description
ProcedureName Name of stored procedure or command text
CommandType 1=Text  2=Table  4=Stored Procedure. (Must specify)
ParamX Input and Output parameters
NORS Returns no recordset
strName Name of parameter (@Name)
inttype Data type of parameter. Refer to the "DataTypeEnum Values" section of the adovbs.inc file. Also use the supplied utility datatype.asp file to determine the data type of your target table if necessary.
intdir 1=input 2=output 3=input/output 4=return
intsize Width (how many characters) of  parameter.
strvalue value of parameter. MUST wrap non-numeric values with single quotes.
...repeat Define another parameter.
 
Example #2005 - MS-SQL  (pubs) Output variable example using full parameters

<%
Set SQL = Server.CreateObject("ASP.db")
SQL.dbQuickProps="3;PUBS;;grid;4,auto,lightgreen"
SQL.dbdat="i22,pubs,sa,sa"
SQL.dbStoredProcCmdParams="(;,)get_sales_for_title,4,NORS;@title,varchar,1,80,'Valley';@ytd_sales,integer,output"
SQL.ASPdb
response.write("Returned Value of ytd_sales = " & Session("SV_SP_3_@ytd_sales"))
%>

Test SP in Pubs - get_sales_for_title

CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title like '%' + @title + '%'
RETURN
GO

This example search for LIKE 'Valley%' in the title field of the titles DB. The returned output is @ytd_sales retrived by the second set of output params.

Example #1 - MS-SQL  (pubs) standard example - reptq1 - On-the-fly w/o parameter

<%
Set X=Server.CreateObject("ASP.DB")
X.dbUnit=3
X.dbMode="Grid"
X.dbDSN="DSN=pubs; UID=sa; PWD="
X.dbStoredProc="reptq1"
X.ASPDB
%>

Example #2 -  MS-SQL  (pubs) standard example - byroyalty - On-the-fly with parameter

<%
Set X=Server.CreateObject("ASP.DB")
X.dbUnit=3
X.dbMode="Grid"
X.dbDSN="DSN=pubs; UID=sa; PWD="
X.dbStoredProc="byroyalty,4,50"
X.ASPDB
%>

Example #3 - MS-SQL  (pubs) standard example - byroyalty - full parameter

<%
Set X=Server.CreateObject("ASP.DB")
X.dbUnit=4
X.dbDBType="SQL"
X.dbMode="Grid"
X.dbDSN="DSN=pubs; UID=sa; PWD="
X.dbStoredProcCmdParams="byroyalty;@percentage,3,1,5,100"
X.ASPDB
%>


Example #4 - MS-SQL  (pubs) standard example - repq3 - On-the-fly with parameter - Multiple Recordsets

<%
Set SQL7=Server.CreateObject("ASP.DB")
SQL7.dbQuickProps= "(|,)3|DSN=pubs;UID=sa;PWD=;||grid|4|||SQL|1000|||False|False"
SQL7.dbStoredProc="reptq3,4,5,50,'business'"
SQL7.dbOptions="CellFontTag=Size=2, HeaderFont=Size=2"
for i=1 to 5 step 2
SQL7.dbStoredProcRS=i
SQL7.dbMagicLayout=i & ";GRID"
SQL7.ASPDB
next
response.write(session("ASPDB_Layout_3_1") & "<BR>")
response.write(session("ASPDB_Layout_3_3") & "<BR>")
response.write(session("ASPDB_Layout_3_5"))
%>

This example retunrs multiple recordsets (1,3 & 5). It only works on MS-SQL.


Example #5 - ORACLE Package - On-the-fly with parameter

<%
Set X=Server.CreateObject("ASP.DB")
X.dbUnit=1
X.dbDBType="SQL"
X.dbDSN="Provider=MSDAORA; data source=dell500; user id=scott; password=tiger"
X.dbStoredProc="(;~){call OraResultSet.empResultSet({resultset 20, o_empno, o_ename, o_job, o_mgr, o_hiredate, o_sal, o_comm, o_deptno})}~1"
X.ASPDB
%>
Example #5 & #6 is extracted from the WROX book "Professional ADO RDS Programming with ASP". Set up the ORACLE empResultSet adn ExFunct Package examples in Scott/Tigerís account and execute the ASPdb code.


Example #6 - ORACLE Function - Full parameter - Return Values

<%
Set Y=Server.CreateObject("ASP.DB")
Y.dbUnit=5
Y.dbDBType="SQL"
Y.dbDSN="Provider=MSDAORA; data source=dell500; user id=scott; password=tiger"
Y.dbStoredProcCmdParams="(;~){? = call ExFunct(?,?)}~1~NORS;Res~139~2~10;Num1~139~1~5~19;Num2~139~1~5~18"
Y.dbDisplay=false
Y.ASPDB
response.write("<B>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") & "</B>")
%>
Note: The returned variable is defined as Res~139~2~10 where 2 = Output. VarName=Res


Example #7 - ACCESS Query - On-the-fly without parameter - [Quarterly Orders by Product]

<%
Set X=Server.CreateObject("ASP.DB")
X.dbDSN="provider=Microsoft.Jet.OLEDB.4.0; data source=e:\vData\NWIND\NWind2000.mdb"
X.dbUnit=1
X.dbMode="Grid"
X.dbDBType="ACCESS"
X.dbStoredProc="[Quarterly Orders by Product],2"

X.dbSumColumn="3"
X.ASPDB

%>
This ACCESS Northwind query contains SQL codes unique to ACCESS only. Any attempt in trying to duplicate the SQL in ASP/ADO will fail. However, the query can be executed via this dbStoredProc call and  the returned recordset is identical to the one in ACCESS.


Example #8 - ACCESS Query - On-the-fly with parameter - [Employee Sales by Country]

<%
Set X=Server.CreateObject("ASP.DB")
X.dbDSN="provider=Microsoft.Jet.OLEDB.4.0; data source=e:\vData\NWIND\NWind2000.mdb"
X.dbUnit=1
X.dbMode="Grid"
X.dbDBType="ACCESS"
X.dbStoredProc="[Employee Sales by Country],2,'1/1/94','1/1/95'"
X.ASPDB
%>

Example #9 - ACCESS Query - full parameter - [Employee Sales by Country]

<%
Set X=Server.CreateObject("ASP.DB")
X.dbDSN="provider=Microsoft.Jet.OLEDB.4.0; data source=e:\vData\NWIND\NWind2000.mdb"
X.dbUnit=9
X.dbMode="Grid"
X.dbDBType="ACCESS"
X.dbStoredProcCmdParams="[Employee Sales by Country], 2; Beginning Date,135,1,16,'1/1/94'; Ending Date,135,1,16,'1/1/96'"
X.ASPDB
%>


Example #10 - Advanced MS-SQL SP

Look at the following stored procedure which allows use to input the WHERE criteria in a select statement. Default value is 'Type LIKE %cook'. Execute this SP in the SQL server Query Analyser by typing -> execute spTest "type LIKE '%bus'" and execute spTest 'price > 20'
CREATE PROCEDURE spTest @strWhere varchar(250)="type like '%cook'"
AS declare 
@tmpSqlMain varchar(255),
@tmpWhere varchar(180)
select @tmpSqlMain='select pub_id, type, title_id, price from titles'
-- get Where clause
select @tmpWhere= @strWhere
-- check if anything in input criteria and add it to the SQL statement if any
if @tmpWhere <>'' 
begin 
select @tmpSqlMain=@tmpSqlMain + ' Where ' + @tmpWhere
end 
-- Execute SQL statement
execute (@tmpSqlMain)

This SP puts ASPdb stored procedure support to the stress test. The following code execute the stored procedure spTest using on-the fly and full parameter methods -
<CENTER>
<%
Set SQL=Server.CreateObject("ASP.DB")
response.write("<h3>Using on the fly - sptest<BR>Input='type LIKE 'bus%''</h3>")
SQL.dbQuickProps= "(|,)1|DSN=pubs;UID=sa;PWD=;||grid|4|||SQL|1000"
'On the fly - see Note below for string type input
SQL.dbStoredProc="sptest,4, 'type LIKE 'bus%''"
SQL.ASPDB
response.write("<h3>Using full Parameters - sptest<BR>Input='price >20'</h3>")
SQL.dbUnit=2
'Full parameter
SQL.dbStoredProcCmdParams="sptest,4;@strWhere,200,1,20,'price > 20'"
SQL.ASPDB
%>
</CENTER>


Notes