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 [email protected] 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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: