Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 6, 2000

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) OUTPUT
AS

SET NOCOUNT ON

SELECT * FROM Categories
SELECT @ItWorked = 'It Worked'

RETURN 0
In the Immediate window I see:
10 
It Worked
This is with ADO 2.1.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date