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.
