Stored procedure does not return value 1

[harmmeijer]

In the query analyzer it works perfect but the sproc will not return its value to my vb program.
Here is the sproc:
CREATE PROCEDURE [mysproc]
@number int,
@@outnum int OUTPUT
AS
insert into test ([testid]) values (@number)
select @@outnum = @number
GO

Here is the vb code:
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim intInteger As Integer
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=****;Initial Catalog=test;Data Source=****"
conn.Open
With comm
.ActiveConnection = conn
.CommandText = "mysproc"
.CommandType = adCmdStoredProc
'Input parameters
.Parameters.Append .CreateParameter("@number", adInteger, adParamInput, , 22)

'Output parameters
.Parameters.Append .CreateParameter("@@outnum", adInteger, adParamOutput, , intInteger)
.Execute , , ADODB.adExecuteNoRecords
' sproc has been executed and the value 22 has been inserted
' intInteger is still 0 WHY????
End With

When I run the sproc in the query analyzer I get the output value:
declare @outnum int
exec mysproc 22, @outnum output
select @outnum as test

output:
(1 row(s) affected)

test
-----------
22

(1 row(s) affected)

 

[harmmeijer]

It is fixed:

.Parameters.Append .CreateParameter("@@outnum", adInteger, adParamOutput)
.Execute , , ADODB.adExecuteNoRecords
' sproc has been executed and the value 22 has been inserted
' not to set intInteger
intInteger = .Parameters("@@outnum&quot

 

[THOMASNG]

Strictly speaking, a "procedure" never returns a value.
One that does is called a "function". Try declaring it a
function.
Also, variables passed "by value" by default - meaning they can't change the original variable. You must EXPLICITLY pass "by reference".
Use VB's HELP button for more details.

 

[CCLINT]

I don't agree....

 

[harmmeijer]

THOMASNG is correct speaking in a pure VB point of view, passing a variable by value does not change the value of the variable passed to a function unless:
myvar = myfunction(myvar)

The sproc is a SQL server stored procedure (it's what they call a stored PROCEDURE).
Thisone can return values (for errorhandling) and return values (called output parameters)

 

[CCLINT]

I quote him: "Also, variables passed "by value" by default".

 

[harmmeijer]

Yes, correct VB passes variables by refrerence (default).
You have to provide byval if you want to pass by value.

 

[NNS1]

I had similar problem with running store procedures with output parameters. You code was very helpfull. Thank you very much!!!

Nelly