Parameter with direction adParamReturnValue

Mike Tholkes

unread,

Feb 10, 1998, 3:00:00 AM2/10/98

to

Has anyone used this parameter direction, adParamReturnValue?

I'm trying to use a sql server stored procedure and I would like to get the
return value of the stored procedure. (In T-SQL, using the RETURN command)

Here is my sql (I'm a beginner at T-SQL, any sql suggestions is
appreciated):
CREATE PROCEDURE apAuthorTitlesCount @au_id char(11), @TitlesCount int
OUTPUT
AS
if (@au_id is Null)
begin
return 1
end
Select @TitleCount = Count(b.title_id)
from titleauthor as a,
titles as b
where
a.title_id = b.title_id
and a.au_id = @au_id
go

Here is my vb code:

' This code does not work, it returns an error saying I have too many
parameters,
' if I take out the return parameter stuff, it works fine, but then how
would I get the
' return value if I needed it?

Dim oCommand As ADODB.Command
Dim oParam As ADODB.Parameter
Dim oConnect As ADODB.Connection
Dim lNumRecords As Long

Set oCommand = New ADODB.Command
Set oConnect = New ADODB.Connection

' Open the connection
oConnect.ConnectionString = m_sConnection
oConnect.Open

' Create parameters
Set oParam = oCommand.CreateParameter("au_id", adChar, adParamInput, 11,
"172-32-1176")
oCommand.Parameters.Append oParam
Set oParam = Nothing

Set oParam = oCommand.CreateParameter("TitleCount", adInteger,
adParamOutput, 11, 0)
oCommand.Parameters.Append oParam
Set oParam = Nothing

' This is my return value parameter
Set oParam = oCommand.CreateParameter("Return", adTinyInt,
adParamReturnValue, 1, 0)
oCommand.Parameters.Append oParam
Set oParam = Nothing

oCommand.ActiveConnection = oConnect

oCommand.CommandText = sSql
oCommand.CommandType = adCmdStoredProc

' Execute the store procedure
oCommand.Execute lNumRecords

MsgBox "Num Param: " & oCommand.Parameters.Count
MsgBox "Output Param Value: " & oCommand.Parameters("TitleCount").Value
MsgBox "Return Value: " & oCommand.Parameters("Return").Value

oConnect.Close

Set oConnect = Nothing
Set oCommand = Nothing
Set oParam = Nothing

Alexander Zelensky

unread,

Feb 10, 1998, 3:00:00 AM2/10/98

to

Mike.
1. In T-SQL return also means "end of program". It means that your SELECT
will never be executed.
2. Unfortunately, some times, when you use IF-ELSE, WHILE, INSERT... in you
SP it does not want to return values in VB, although it is fine in iSQL.
Worse part that I could not find what exactly you may or may not to use in
SP.
In your case I would specify default au_id = "" and return it after SELECT.

Alex
al...@distantdata.com
Visit us at www.distantdata.com

Mike Tholkes wrote in message ...

Mike Tholkes

unread,

Feb 10, 1998, 3:00:00 AM2/10/98

to

Thank you Alexander. It works great !!!!!

But I'm still wondering what the parameter direction adParamReturnValue is
meant to be used for (Whether it works with sql server or not)? Just to put
my mind at ease.

Richard Hennessy

unread,

Feb 17, 1998, 3:00:00 AM2/17/98

to

Mike Tholkes wrote:

Hi Mike

adParamReturnValue is for the return value of the stored procedure, e.g.
in your example
if @au_id is NULL you return a 1. The return parameter is always the
first parameter created.
If you want to pass back a value in @Titlecount then create this
parameter as
adParamOutput or adParamInputOutput.

regards

Richard Hennessy