The ASPSmith Articles, ASPAlliance.com

This article covers simply HOW to use stored procedures with ADO. WHY you should do so is covered in another article, but suffice to say that you should. SQL code in your ASP page is BAD. Remember that and you'll do fine. Now let's look at some code.

The easiest way to call a stored procedure is through the connection object. This can be as simple as four lines of code:

	Dim objConn
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open Application("Connection_String")
	'Call the stored procedure to increment a counter on the page
	objConn.Execute "exec sp_AddHit"

This case assumes there are no parameters, no results, and no error handling. Most of the time, you'll need more than this. (and of course, if this is all you're doing, then don't forget the 5th/6th lines of code -- CLOSE the connection and set it to NOTHING!)

Adding parameters is easy, you just append them to the sql command string. The .Execute line then becomes:

	objConn.Execute "exec sp_AddHit 'http://www.aspalliance.com', 1"

Separate parameters with commas. Don't use parantheses. But what if you want to return the results in a recordset?

In this case, we simply use rs.open or set rs = conn.execute to populate our recordset. Otherwise everything is the same. Here is the code using parameters to populate a recordset:

	Dim objConn
	Dim objRs
	Set objConn = Server.CreateObject("ADODB.Connection")
	Set objRs = Server.CreateObject("ADODB.Recordset")
	objConn.Open Application("Connection_String")
	'Call the stored procedure to increment a counter on the page
	objRs.Open "exec sp_ListArticles '1/15/2001'", objConn
	'Loop through recordset and display each article

As you can see, this is still really short and sweet. Now it's time to get tricky. What if you want to insert a row into the database and then return that row's ID? Well, there are several techniques, but one of the best ways is with a stored procedure and an output parameter (for more, see Using Stored Procedures For Data Access and Retrieving the Identity / Autonumber value). This uses the ADO Command object, which you can read more about here.

Any time you need to get data back from a stored procedure via a parameter, you need to invoke it using the ADO Command object. The sample below demonstrates the minimal properties and methods required to invoke a stored procedure call and return a value in an output parameter.

Dim objConn
Dim objCmd

'Instantiate objects
Set objConn		= Server.CreateObject("ADODB.Connection")
set objCmd		= Server.CreateObject("ADODB.Command")
conn.Open Application("ConnectionString")

With objCmd
    .ActiveConnection = conn 'You can also just specify a connection string here
    .CommandText = "sp_InsertArticle" 
    .CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
    
    'Add Input Parameters
    .Parameters.Append .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
    .Parameters.Append .CreateParameter("@url", adVarChar, adParamInput, 255, url)
    .Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 99, url)
    .Parameters.Append .CreateParameter("@description", adLongVarChar, _
		adParamInput, 2147483647, description)
    
    'Add Output Parameters
    .Parameters.Append .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
        
    'Execute the function
    'If not returning a recordset, use the adExecuteNoRecords parameter option
    .Execute, , adExecuteNoRecords
    link_id = .Parameters("@link_id")
End With

The source for this stored procedure is:

Create PROCEDURE dbo.sp_InsertArticle
(
	@columnist_id int,
	@url varchar(255),
	@title varchar(99),
	@description text
   	@link_id int OUTPUT
)
AS
BEGIN
	INSERT INTO dbo.t_link	(columnist_id,url,title,description)
	VALUES (@columnist_id,@url,@title,@description)

    SELECT @link_id = @@IDENTITY
END

Notice a few things about this call. First, the commandtype must be set to adCmdStoredProc. Second, the parameters are added in the same order and with the same names as the parameters in the stored procedure itself. Note that the data types match as well. Next observe that the third parameter sets whether a parameter is defined as input or output (this is designated with the OUTPUT keyword in the stored procedure definition). Finally, the most efficient way to retrieve a scalar value from a database is via a stored procedure output parameter. In this case, we are getting the id of the link we just added. Note that the .Execute command is using the adExecuteNoRecords option. This allows ADO to optimize its query by avoiding the need to create and populate a recordset object, which saves a significant amount of bandwidth and processing.

By the way, if you've already written some SP's, or are using a project that has, and you want to see what they are doing in the SPs and you don't have access to anything but your trusty ASP pages, you can view the source of the proc by using this command:

  sp_helptext sp_MyStoredProcedure

Similarly, although not needed quite as often, you can view just the dependencies of the SP by using sp_depends. You can get more info on this at http://support.microsoft.com/support/SQL/Content/inprodhlp/_create_procedure.asp.