Pass That Parameter Over Here! - Executing our stored procedure
(Page 3 of 4 )
To execute our stored procedure, create a new ASP page and save it as storedproc.asp. Enter the following code into storedproc.asp:
1. <html>
2. <head>
3. <title> Using Parameters with stored procedures </title>
4. </head>
5. <body>
6. <!-- METADATA Type="TypeLib" File="c:\program files\common files\system\ado\msado15.dll" -->
7.
8. <%
9.
10. dim strState
11. dim dbConn
12. dim dbComm
13.
14. strState = "CA"
15.
16. set dbConn = Server.CreateObject("ADODB.Connection")
17. set dbComm = Server.CreateObject("ADODB.Command")
18.
19. dbConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=pubs; UId=sa; Pwd="
20.
21. dbComm.ActiveConnection = dbConn
22. dbComm.CommandType = adCmdStoredProc
23. dbComm.CommandText = "sp_GetAuthorCountByState"
24.
25. dbComm.Parameters.Append dbComm.CreateParameter("stateName", adChar, adParamInput, 2, strState)
26. dbComm.Parameters.Append dbComm.CreateParameter("numResults", adInteger, adParamReturnValue)
27.
28. dbComm.Execute
29.
30. Response.Write "The number of authors whose state matched " & strState & " was " & dbComm.Parameters(1).Value
31.
32. %>
33. </body>
34. </html>The code above uses ADO connection and command objects to connect to and execute queries against SQL Server 2000.
On line 6 we’re using a META tag to import all the database constants including connection options, lock types, etc from the MSADO15.dll type library located in the c:\program files\common files\system\ado folder. This saves us the hassle of having to remember the values for the CommandType parameter of our command object, etc because it is already pre-declared for us.
On line 14 we set the value of the strState variable to “CA”. We will be using this variable as the value of our input parameter.
Lines 16-19 declare our connection and command objects and open a connection to our database server. On lines 21-23 we set the ActiveConnection parameter of our command object to point to objConn (Our open connection object), set our command object to parse all of its commands as stored procedures and finally let our command object know that we will be working with the stored procedure named sp_GetAuthorCountByState.
Remember back to when we created our stored procedure in Query Analyser? We declared one input parameter and one output parameter. Our input parameter was used to “pass in” the value of the state that we wanted our authors to live in and the output parameter was used to hold the number of authors who lived in that state.
On lines 25 and 26, we’re creating our input and output variables respectively. We simply call the append method of the parameters collection of our command object and parse a new parameter object using the CreateParamater method of our command object.
In our ASP script, we create a new input parameter named stateName. The stateName parameter is of type adChar (character) and is two bytes long. The last value used to create our input parameter is the actual value that we want to assign to it. In our case, we are assigning the value of the strState variable to our parameter.
Creating our output parameter is a bit simpler because we don’t need to specifiy every value for it. We create our new output parameter and name it numResults. It’s of type adInteger (integer). The last value specified for our output parameter, adParamReturnValue lets ADO know that we are expecting our parameter to contain a return value after we have execute our stored procedure.
The two parameters that we have passed to our stored procedure are indexed so that we can refer to them easily from the parameters collection of our command object. The input parameter, stateName has an index of zero. The output parameter, numResults has an index of one. If we had another parameter, it would have an index of two, etc.
It should be no surprise that line 28 executes our stored procedure. If all goes well, our output parameter will contain the number of rows whose state value matched the value of our input parameter (“CA”). Lastly, we use the Response.Write method to print out the number of results (dbComm.Paramaters(1).Value) returned and the state (dbComm.Paramaters(0).Value) that we were filtering on.
If the stored procedure executed OK, you should be greeted with a blank page containing the number of results that matched our query and the actual value of the state that we were searching for:
Next: Conclusion >>
More ASP Articles
More By Mitchell Harper
