Data Access and Transactions

Stored Procedures

If you find yourself performing complex data manipulation, consider organizing database dependencies and rules into stored procedures.

Stored procedures are precompiled queries stored on the database server. They can simplify development and significantly speed up complex queries. When a stored procedure is called, it controls which operations are performed and which database fields are accessed. A single stored procedure can even execute multiple queries.

Stored procedures have explicitly defined parameters, each with a specific data type, direction, and size. Before calling a stored procedure, the Parameter collection of the Command object must be prepared to precisely match the number and type of parameters defined for the procedure on the server. Although you can request the complete Parameter collection by calling the Refresh method, building the collection parameter by parameter is preferred. Calling this method results in faster execution and avoids a network round-trip to the server. (Also, some providers do not support populating the Parameter collection with the Refresh method.) The code, however, ends up looking a bit more complex, as shown here:

<%
   Set cm = Server.CreateObject("ADODB.Command")
   cm.CommandText = "AddCustomer"
   cm.CommandType = adCmdStoredProc
   Set p = cm.Parameters
   p.Append cm.CreateParameter("@Name", adChar, adParamInput, 40)
   p.Append cm.CreateParameter("@Address", adChar, adParamInput, 80)
   p.Append cm.CreateParameter("@City", adChar, adParamInput, 20)
   p.Append cm.CreateParameter("@State", adChar, adParamInput, 2)
   p.Append cm.CreateParameter("@Zip", adChar, adParamInput, 11)
   cm("@Name") = Trim(Request.Form("Name"))
   cm("@Address") = Trim(Request.Form("Address"))
   cm("@City") = Trim(Request.Form("City"))
   cm("@State") = Trim(Request.Form("State"))
   cm("@Zip") = Trim(Request.Form("Zip"))
   cm.Execute
%>

See the following: