devxlogo

ADO with Stored Procedures

ADO with Stored Procedures

Question:
How do I get both output parameters and output recordsets from the SQL7 stored procedures I call from ASP via the ADO command object?

Answer:
It is possible to return both data in output parameters and a recordset. Here’s the VB/ADO code to create Command and Recordset objects and call a stored procedure. The stored procedure returns records and a value in its output variable:

Dim adoRS As New ADODB.Recordset    Dim adoCommand As New ADODB.Command        With adoCommand        .CommandText = "usp_GetCategories"        .CommandType = adCmdStoredProc        .ActiveConnection = "NorthwindLocal"        .Parameters.Append .CreateParameter("RETURN_VALUE", 
adInteger, adParamReturnValue, , Null) .Parameters.Append .CreateParameter("@ItWorked",
adVarChar, adParamInputOutput, 255, Null) End With With adoRS adoRS.CursorType = adOpenStatic adoRS.CursorLocation = adUseClient adoRS.Open adoCommand End With Debug.Print adoRS.RecordCount Debug.Print adoCommand.Parameters("@ItWorked").Value

Here’s the stored procedure that should be put in the Northwind database:

CREATE PROCEDURE usp_GetCategories@ItWorked varchar(255) OUTPUTASSET NOCOUNT ONSELECT * FROM CategoriesSELECT @ItWorked = 'It Worked'RETURN 0

In the Immediate window I see:

10 It Worked

This is with ADO 2.1.

See also  Does It Make Sense to Splurge on a Laptop?
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist