Execute a Stored Procedure Without an Explicit ADO Command Object

The following code shows you how to execute a Stored Procedure (called MyStoredProcedure) in ASP without creating an explicit ADO Command object. The Stored Procedure expects two parameters: @FirstParam (of datatype int) and @SecondParam (of datatype varchar). Modify the parameter list to suit your requirements
The Stored Procedure should look similar to this:

CREATE PROCEDURE MyStoredProcedure

  @FirstParam int,
  @SecondParam varchar(10)

AS

  Your SELECT statement here

And the code in ASP page should look like this:

<%
  Dim MySQLStatement
  Dim MyConnection
  Dim MyRecordset

  Dim intValue
  Dim stringValue

  intValue = 10
  stringValue = "Test Value"

  MySQLStatement = "exec MyStoredProcedure @FirstParam=" & intValue & _
          ", @SecondParam='" & stringValue & "'"
  Set MyConnection = Server.CreateObject("ADODB.Connection")
  MyConnection.Open MyConnectionString
  Set MyRecordset = MyConnection.Execute(MySQLStatement)

  If Not MyRecordset.EOF Then
    Do While Not MyRecordset.EOF
      Response.Write("Record " & MyRecordset.Fields(0).Value & " - " _
           & MyRecordset.Fields(2).Value & "<br />")
      MyRecordset.MoveNext()
    Loop
  Else
    Response.Write("No Records Found")
  End If
%>

Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Tuesday, March 13, 2007 1:35:40 PM Mikesdotnetting said:
I prefer the Procedure-as-method-of-the-connection approach:

Replace:

MySQLStatement = "exec MyStoredProcedure @FirstParam=" & intValue & _
          ", @SecondParam='" & stringValue & "'"
  Set MyConnection = Server.CreateObject("ADODB.Connection")
  MyConnection.Open MyConnectionString
  Set MyRecordset = MyConnection.Execute(MySQLStatement)

With:

Set MyConnection = Server.CreateObject("ADODB.Connection")
Set MyRecordset = Server.CreateObject("ADODB.RecordSet")
MyConnection.Open MyConnectionString
MyConnection.MyStoredProcedure intValue, stringValue, MyRecordset

The recordset object is always passed as the last argument, and this approach removes the need to mess about with delimiting the parameter values passed into the SQL

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.