Stored Procedures with ASP

Next we'll look at the ASP to actually excute the procedure:

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = getConnection()
cmd.CommandText = "spGetCategoriesByProject"
cmd.CommandType = adCmdStoredProc

Set projectID = cmd.CreateParameter("@projID", adInteger, adParamInput)
projectID.Value = projID
cmd.Parameters.Append projectID

Set objID = cmd.CreateParameter("@objID", adInteger, adParamInput)
objID.Value = objectID
cmd.Parameters.Append objID

Set rs = cmd.Execute
GetCategories = rs.GetString(, , "~", "#")
Set rs = Nothing

Notice the call to the getConnection() function when setting the active connection for the command object. This is simply a function that returns an open Connection object:

Public Function getConnection()
	Set getConnection = Server.CreateObject("ADODB.Connection")
	getConnection.Open Application("connstring")
End Function

After we establish the Command object we will declare the input parameters that the procedure expects and set a value for them.
Then all we have to do after this is actually excute the stored procedure and get the results into a recordset.
In this case we are simply using the ADO GetString method to return the recordset as a string.

<< Back