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 by using ADO from within an ASP script.
This script first creates an instance of the Connection object and uses it to open an OLE DB connection with the sample database, pubs. 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 want 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 were 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 object variable oRs defined earlier in the script. The first record of the resultant recordset is displayed.
Important 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.
The VBScript and JScript versions of this script are available in the IIS samples directory, at ...\asp\database\StoredProcedures_VBScript.asp and ...\asp\database\StoredProcedures_JScript.asp.