[Resolved] Return value of Stored Procedure

[naruponk]

Hi,

This is inside stored procedure
......
Select @Result
Return @Result
......

Then i call this stored procedure from VB

with cm
......
......
......
end with
with rs
......
......
.open cm
msgbox .recordcount
end with

I got an error that "Operation is not allow when object is closed"
I'm surely that i have open cm already, when i change to other
stored procedure it works fine. I think the problem is i dont know
how to use stored procedure which is using RETURN

I have try this stored procedure in SQL analyzer, works fine.
Can any one give me the right way to solve this problem?

Thanks for advance

[Blade]

Are you doing anything else inside the proc before the SELECT @Result statement ie. inserts or updates?

If so, try adding SET NOCOUNT ON as the first statement and see if that resolves the issue.

[naruponk]

Same error.

@Result is what i want, i have process it inside Stored Procedure
no insert/update it is just select statement.

One more question is how to exit Stored Procedure when it get some value and return a vlue together like Exit Sub, Exit Function?

[Blade]

To exit a Stored Proc use RETURN or RETURN (value) .

[naruponk]

with cm
......
......
......
end with
with rs
......
......
.open cm
msgbox .fields!Test
end with

This will be unknown fields even i type in stored proc that
select @Result as Test.
How to retreive a field from this stored procedure?

Thanks Blade

[PilgrimPete]

Are you able to post the whole stored proc?
It might help us get to the problem faster if you can...

[szlamany]

Looks to me like you are using a RECORDSET object and not a COMMAND object.

You have no recordset coming back from the SPROC (from what I can tell by what you posted).

Use the COMMAND object - execute the COMMAND to call the SPROC.

The command object will contain the return parameters and any output parameters.

We prefer to use output parameters - here's an example:

objCmd.CommandText = "AppConnect_Insert "
objCmd.ActiveConnection = gCn
objCmd.CommandType = adCmdStoredProc
        
objCmd.Parameters.Append objCmd.CreateParameter("@RetConnId", adInteger, adParamOutput, 8)
objCmd.Parameters.Append objCmd.CreateParameter("@RetOpenServTime", adChar, adParamOutput, 23)
objCmd.Parameters.Append objCmd.CreateParameter("@ClientTime", adChar, adParamInput, 23 _
                                                                , Format(Now, "yyyy-mm-dd hh:mm:ss.000"))
objCmd.Parameters.Append objCmd.CreateParameter("@AppEXEName", adVarChar, adParamInput, 100, App.EXEName)
objCmd.Parameters.Append objCmd.CreateParameter("@AppPath", adVarChar, adParamInput, 100, App.Path)
objCmd.Parameters.Append objCmd.CreateParameter("@AppTitle", adVarChar, adParamInput, 100, App.Title)
objCmd.Parameters.Append objCmd.CreateParameter("@AppComments", adVarChar, adParamInput, 100, App.Comments)
objCmd.Parameters.Append objCmd.CreateParameter("@AppMajor", adInteger, adParamInput, 8, App.Major)
objCmd.Parameters.Append objCmd.CreateParameter("@AppMinor", adInteger, adParamInput, 8, App.Minor)
objCmd.Parameters.Append objCmd.CreateParameter("@AppRevision", adInteger, adParamInput, 8, App.Revision)
objCmd.Parameters.Append objCmd.CreateParameter("@FLAGS", adVarChar, adParamInput, 100, "RES=[" & ScreenResolution & "]")
   
objCmd.Execute

glngConnId = objCmd.Parameters(0)
gstrOpenServTime = objCmd.Parameters(1)

[szlamany]

Here's the SPROC itself if you need to see what's going on in that...

--FIS
Use Acctfiles
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Drop Procedure AppConnect_Insert
Go

Create Procedure AppConnect_Insert 
@RetConnId int OUTPUT
, @RetOpenServTime char(23) OUTPUT
, @ClientTime char(23)
, @AppEXEName varchar(100)
, @AppPath varchar(100)
, @AppTitle varchar(100)
, @AppComments varchar(100)
, @AppMajor int
, @AppMinor int
, @AppRevision int
, @Flags varchar(100)

as

Declare @ServerTime datetime
DECLARE @Rollback int
DECLARE @Count int
Declare @HostName varchar(100)

BEGIN TRAN

Set @ServerTime = GETDATE()

Set @Flags=IsNull(@Flags,'')
Set @HostName='HOST=['+HOST_NAME()+']'
If Len(@HostName)+Len(@Flags)<=100 Set @Flags=@HostName+' '+@Flags

INSERT INTO AppConnect_T
(UserID, OpenServTime, OpenClientTime, AppEXEName, AppPath
,AppTitle, AppComments,AppMajor, AppMinor, AppRevision
, Status, Flags)
Values
(SYSTEM_USER, @ServerTime, @ClientTime, @AppEXEName, @AppPath
, @AppTitle, @AppComments, @AppMajor, @AppMinor, @AppRevision
, 'O', @Flags)

Select @RollBack = @@error
set @RetOpenServTime = Convert(Char(23),@ServerTime, 121)
set @RetConnId = @@IDENTITY

IF @Rollback <> 0
BEGIN
	ROLLBACK TRAN
END
ELSE
BEGIN
	COMMIT TRAN
END

GO

GRANT EXEC ON AppConnect_Insert TO AcctfilesUser
GO

--Set @ident = @@IDENTITY

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[naruponk]

Thanks PilgrimPete,

I think it is unneccessary to post whole stored proc.
It is too much and need to use many of stored procedure together,
also database.

However I'm surely that the point is i might wrong to return a value

Select @Result
Return @Result

--this 2 lines is what i'm using

I have try other stored procedure with one line of code inside stored procedure still get same error.
you might try to create simple stored procedure which is using return keyword
to return a values

Thanks

[naruponk]

Dear szlamany,

Thanks for reply
What does SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON do?
I can't run your Stored procedure cause i have no some of your stored procedure can you give me more shortly?

Normally i use command to open recordset from stored procedure, works fine.
but i can't open a recordset (using command) from stored procedure which is using RETURN keyword

Thanks everyone

[PilgrimPete]

OK. I have created a stored proc like this:

create proc test
as
declare @Result int

set 	@Result = 99
Select 	@Result
Return 	@Result

and called it like this:

private Const connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabase;Data Source=myServer"
 
Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = connString
        .Open
    End With
    
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "test"
    End With
    
    Set rst = New ADODB.Recordset
    With rst
        .Open cmd
        Debug.Print .Fields(0).Value
    End With
End Sub

and I get 99 output to the debug window. I'm still none the wiser
Sorry.

[techgnome]

Nargh... Either SELECT the value or RETURN it... no need to do both...
OK, now, to get the RETURN value of a stored proc, it goes like this:

cmd.Parameters.Append cmd.CreateParameter("@ReturnValue",adInteger,[b]adParamReturnValue[/b])

Creating & setting this as the first parameter in your list should do the trick. The @ReturnValue can be anything as long as it's different from the other parameter names. After .Executing the command, you can get the value just like any other output parameter.

Tg

[szlamany]

Nargh... Either SELECT the value or RETURN it... no need to do both...

This is really good advice - as was adding the SET NOCOUNT ON - which is one of our SQL Rules to Live by.

RECORDSET data and output parameter data is in two different objects in ADO - and they cannot be touched at the same time.

You have to process RS, then look at output parameters, then look at RS (if you have more than one RECORDSET in the SPROC), then look at output parameters again.

SET NOCOUNT ON will make sure that the annoying "1 row affected" message doesn't get in the way of RECORDSET processing or output parameter processing.

[szlamany]

Dear szlamany,

Thanks for reply
What does SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON do?
I can't run your Stored procedure cause i have no some of your stored procedure can you give me more shortly?

Normally i use command to open recordset from stored procedure, works fine.
but i can't open a recordset (using command) from stored procedure which is using RETURN keyword

Thanks everyone

Another one of our rules is to SCRIPT all DB objects in TEXT FILES that end in .SQL and execute them in QUERY ANALYZER to load them into the database.

That SET QUOTED stuff is just boilerplate that we have in our SCRIPT files - something we saw that MS did in their scripts.

[PilgrimPete]

Originally Posted by techgnome
Nargh... Either SELECT the value or RETURN it... no need to do both...

This is really good advice - as was adding the SET NOCOUNT ON - which is one of our SQL Rules to Live by.

RECORDSET data and output parameter data is in two different objects in ADO - and they cannot be touched at the same time.

You have to process RS, then look at output parameters, then look at RS (if you have more than one RECORDSET in the SPROC), then look at output parameters again.

SET NOCOUNT ON will make sure that the annoying "1 row affected" message doesn't get in the way of RECORDSET processing or output parameter processing.

I agree totally... I was just trying to answer what I thought was a hypothetical question, and to prove that given the posted code, it would actually work.
I'd never actually advocate the use of a recordset to return a single value; though I generally lean towards using an output parameter to return data values, and save the adParamReturnValue parameter to return the success (or otherwise) of my proc - not least because you are restricted to returning ints in a RETURN statement. However, that's just down to personal taste and coding standards I guess...

[naruponk]

Thanks a lot for help guys

Even i set Return @Result at a first line of Stored Procedure
I still getting same error "....When object is closed"
The problem might be my Stored Procedure

Decalre @Result

SET @Result=99
Return @Result

........ below is too much of proceed ...........
............................................................

If my Stored Procedure is look like this the process will end at
Return @Result?

Can i use Return keyword without select keyword before?

I have found that after EXECUTE Other_StoredProc
I will unable to open a recordset even i use select keyword,
so .... how can i retrive a recordset?

[PilgrimPete]

If you have a return statement at the top of your procedure, then it will do just that: Return.
Nothing after the 'Return' will get executed. So, in your latest example there is no recordset created, so none will be returned.

In order to get at your return value, you'll need to use the example that techgnome posted.

I'm still not sure why you want a recordset and a return parameter containing the same value...

[naruponk]

I'm still not sure why you want a recordset and a return parameter containing the same value...

I just want to be able to retreive a value which is return from stored procedure and using ADO to retreive it. (Return value is after execute Other_StoredProc line in Stored Proc)

Originally Posted by techgnome
cmd.Parameters.Append cmd.CreateParameter("@ReturnValue",adInteger,adParamReturnValue)

I try your code but i got another error "Formal parameter @Result was defined as OUTPUT but the actual parameter not declared OUTPUT."

Can you tell me how should i declare a variable in my stored procedure
to able to return a value by using ADO

Thanks everyone

[PilgrimPete]

Something like this:

create proc test
as
declare @Result int

set 	@Result = 99
Return 	@Result

private Const connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabase;Data Source=myServer"
 
Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
 
    
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = connString
        .Open
    End With
    
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "test"
        .Parameters.Append cmd.CreateParameter("@ReturnValue",adInteger,adParamReturnValue)
        .Execute Options:=adExecuteNoRecords
        Debug.Print .Parameters(0).Value
    End With
 
    cnn.Close
    Set cmd = Nothing
    Set cnn = Nothing
End Sub

Can you tell me how should i declare a variable in my stored procedure to able to return a value by using ADO

You don't need to declare an output parameter if you are using a return value. This proc will do the same as the previous one:

create proc test
as
-- do absolutely nothing, then return 99
Return 	99

[naruponk]

More better

I got an error that "Arithmetic overflow converting expression to data type smalldatetime"

My stored procedure is requires 3 parameter.
1. @A bigint,
2. @B smalldatetime
3. @C smalldatetime

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "WN1_GetIFNPV"
.Parameters.Append .CreateParameter("@A", adBigInt, adParamReturnValue)
.Parameters.Append .CreateParameter("@A", adBigInt, adParamInput, , 1)
.Parameters.Append .CreateParameter("@B", adDate, adParamInput, , "04/04/2005")
.Parameters.Append .CreateParameter("@C", adDate, adParamInput, , "04/04/2005")
.Execute Options:=adExecuteNoRecords
Debug.Print .Parameters(0).Value
End With

How should i give parameters as correct format?

[PilgrimPete]

I'm not sure really - I generally use datetime, not smalldatetime, but you might try adDBDate instead of adDate to see if that helps.

[BTW I wouldn't call your return parameter the same thing as your first input parameter.]

[naruponk]

[BTW I wouldn't call your return parameter the same thing as your first input parameter.]

Did you mean i should remove .Parameters.Append .CreateParameter("@A", adBigInt, adParamInput, , 1) ?

[PilgrimPete]

Well. It depends. You can remove it - but you'll also need to remove it from the stored procedure, or just rename it like this:

.Parameters.Append .CreateParameter("RETURN", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@A", adBigInt, adParamInput, , 1)
.Parameters.Append .CreateParameter("@B", adDate, adParamInput, , "04/04/2005")
.Parameters.Append .CreateParameter("@C", adDate, adParamInput, , "04/04/2005")

...but it depends on how you are using it really.

[naruponk]

"Arithmetic overflow converting expression to data type smalldatetime"

I'm using a code which i posted really.

Parameters.Append .CreateParameter("RETURN", adInteger, adParamReturnValue)

Is return value should be 1st parameter?

[naruponk]

I have create new stored proc which is requires 1 parameter only (@A)

.Parameters.Append .CreateParameter("@A", adBigInt, adParamInput, , 1)
.Parameters.Append .CreateParameter("@Result", adBigInt, adParamReturnValue)

above code will give me that ""Stored Proc ... has too many agrument specific"

.Parameters.Append .CreateParameter("@Result", adBigInt, adParamReturnValue)

This will give me that " ... @A not supplied"
Am i giving incorrect format?

[szlamany]

I'm pretty sure that the RETURN {int} T-SQL command does not return that value in the PARAMETER collection.

I cannot find backup for this in MSDN - I've got a good ADO book at work that I'll check in 30 minutes...

Why are you so stuck on using RETURN {int} and not simply creating a list of INPUT and OUTPUT parameters? My example showed how to do that quite easily.

The error you keep saying you are getting sounds more like a VB/ADO side error - that the object is not properly created and the SPROC isn't even executing.

BTW - we decided at the start of our development project in VB/ADO/SQL that using LONG (VB) and INT (SQL) was the only integer style we would support. DATETIME also - no small datetime. Supporting lots of different datatypes in the PARAMETER logic is too complex.

[PilgrimPete]

I'm pretty sure that the RETURN {int} T-SQL command does not return that value in the PARAMETER collection.

I cannot find backup for this in MSDN - I've got a good ADO book at work that I'll check in 30 minutes...

Why are you so stuck on using RETURN {int} and not simply creating a list of INPUT and OUTPUT parameters? My example showed how to do that quite easily.

The error you keep saying you are getting sounds more like a VB/ADO side error - that the object is not properly created and the SPROC isn't even executing.

BTW - we decided at the start of our development project in VB/ADO/SQL that using LONG (VB) and INT (SQL) was the only integer style we would support. DATETIME also - no small datetime. Supporting lots of different datatypes in the PARAMETER logic is too complex.

I agree with the OUTPUT parameters idea szlamany - sorry missed that bit in your previous post when I posted this:

...though I generally lean towards using an output parameter to return data values, and save the adParamReturnValue parameter to return the success (or otherwise) of my proc...

naruponk - the adParamReturnValue parameter has to be the first you add to the parameters collection in the VB code, otherwise you will get the error you report.

[naruponk]

Ok .... look works
Thanks for ideas & helps guys