Stored procedures, such as those provided by Microsoft® SQL Server, are the keys to making large, mission-critical database-applications function smoothly and efficiently. This example demonstrates how you can access this functionality using ADO from within an ASP script.
This script first creates an instance of the Connection object and uses it to open an ODBC connection with the SQL IISSDK database. A special object, called a Command object, is created next. The Command object's CommandText property is set to the string of the command you wish to issue, which for this sample is the name of a stored procedure, ByRoyalty. The Command object Parameters property provides a collection of Parameter objects, and this script uses the Append method to add a new parameter to the collection.
Once CreateParameter has been used to name and configure the parameter instance, the parameter name assigned can be used to access the value of that parameter directly, as if the Command object itself was a collection. Thus
oCmd("@Percentage") = 75
assigns the value 75 to the parameter that the script has labeled as @Percentage. The Command object's Execute method is invoked, and the resulting recordset is assigned to the previously created object variable oRs. The first record of the resultant recordset is displayed.
Note SQL Server must be installed, and configured properly, on the same machine on which IIS is running in order for this sample to work correctly. In addition, the IISSDK data source must also be configured properly.