Return Value in a Stored Procedure

[frank niedermeyer]

Hello Newsgroup !

My Tools are:
Windows 2000, VBA(Access 2000) and MS SQL Server 7.0

I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"
with parameters a,b

In my VBA Code i wrote:

Dim par As New ADODB.Parameter

Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "[prcSucheUNRWIAEStichtag]"

Set par = Cmd.CreateParameter("@a", adInteger)
Cmd.Parameters.Append par

Set par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)
Cmd.Parameters.Append par

........
Cmd.Parameters(0) = a
Cmd.Parameters(1) = b

Set rsTemp = Cmd.Execute

Now my Problem is the following:

there is an error in the stored procedure and i want to use something like
this:

if @idontknow = '000000000'

Begin
return(1) -- Something <>0
end

How can i use this return value in my VBA code ? Maybe i should ask this
question in an other Newsgroup. Please
let me know in which one

Greetings
Frank

[DMAC]

>

How can i use this return value in my VBA code ? Maybe i should ask this
question in an other Newsgroup. Please
let me know in which one

you have two main choices, declare an ado paramater specifically as a
return parameter and it will receive the value returned by the sproc,
eg

Set par = Cmd.CreateParameter("ret", adInteger, adParamReturnValue)
Cmd.Parameters.Append par

On completion of your sproc you can access this parameter via
cmd.parameters(0) or cmd.parameters("ret"). NOTE for return parameters
it must be the FIRST declared and appended parameter.
The second choice is to declare a variable('s) in your sproc as output
and then have an ado parameter which is also declared as ouptut to
receive this value. eg
Set par = .CreateParameter("intOutput", adVarChar, adParamOutput, 30).
The second method has the obvious advantage of returning data other
than an integer, eg above it is expecting the return of a variable
declared in the sproc as varchar(30) output.

Dave

[Lyndon Hills]

On Tue, 30 Sep 2003 15:49:50 +0200, "frank niedermeyer"
<fn**********@aareon.com> wrote:

Hello Newsgroup !

My Tools are:
Windows 2000, VBA(Access 2000) and MS SQL Server 7.0

I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"
with parameters a,b

In my VBA Code i wrote:

Dim par As New ADODB.Parameter

Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "[prcSucheUNRWIAEStichtag]"

Set par = Cmd.CreateParameter("@a", adInteger)
Cmd.Parameters.Append par

Set par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)
Cmd.Parameters.Append par

.......
Cmd.Parameters(0) = a
Cmd.Parameters(1) = b

Set rsTemp = Cmd.Execute

Now my Problem is the following:

there is an error in the stored procedure and i want to use something like
this:

if @idontknow = '000000000'

Begin
return(1) -- Something <>0
end

How can i use this return value in my VBA code ? Maybe i should ask this
question in an other Newsgroup. Please
let me know in which one

Greetings
Frank

I don't know about access but is SQL Server you could do 2 things;

1) Create an output parameter and assign your value to that.
2) Raise an exception which should be visible in your code, that you
put in a try..catch block.