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

<% 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 %>

<% 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 %>

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

• Any input parameters that include 'string' MUST be wrapped by a pair of single quote. For example - type LIKE '%bus' MUST be input as 'type LIKE 'bus%''. ASPdb will strip the external single quote and indentify the variable is of type char.
• ADO 2.x must be installed in order to use OLE-DB providers in the DSN. For users still using ADO 1.5, modify the DSN to use ODBC.
• All MS-SQL and ACCESS examples are part of the standard installation. Setup the appropriate DSN before using the example code.
• The ORACLE examples are extracted from the WROX's book "Professional ADO RDS Programming with ASP". Install all the examples into scott / tiger's account and execute the included asp files in the ORACLE chapters. After all the example asp files in the book execute without error, then run the above ASPdb examples.
• Try to use CommandText (CommandType=1) instead of Store Procedure   (CommandType=4) for ORACLE.
• The sequence of the parameters must correspond to the (?) of the function. For example, the first (?) is the returned value of the function and therefore the first parameter (Res) of the property must correspond to this (?).
• Parameter variable returned in full parameter will be stored as - session("SV_SP_Unit_VarName"). A return parameter is one that has a intdir value of 2, 3 or 4.
• ACCESS has no stored procedures. If a query has no input parameters, it can be opened as a regular table. If the query contains input/output parameters then dbStoredProc or dbStoredProcCmdParams could be used to execute the query.
• At this time there is no support for column sorting and filtering for SP. It is assumed that all the data shaping is done within the SP. The result from the SP query is expected to be exported as an object to be placed onto the template.