returning a value from Stored procedure

[davejam]

Good afternoon, not sure if what i'm doing is the correct way about it but here goes.

I have a simple stored procedure which compairs tables, updates if needed then returns a value.

I have a form that i need to run the stored procedure and pull the return value so i can update a field on the page.

I have seen examples of using recordsets with stored procedures and in my case they have failed, i think this is because i'm running several select / update statements and i want to return a single value and not a recordset.

Not sure if i've declared all i need to within my stored procedure, Is there a simple way to do this,

heres my sp - i'm passing the recID variable from my form
CREATE PROCEDURE [dbo].[SP_StatusUpdate]
@recID as int
AS
declare @Var23 as int
declare @Var24 as int

select top 1 @Var23 = Astatus from dbo.tableA where ArecID = @recID order by Astatus
Select @Var24 = Bstatus from dbo.tableB where BrecID = @recID
if @Var23 <> @Var24
update dbo.tableB set Bstatus = @var23 where BrecID = @recID

return @Var23
GO

My Form command code
Private Sub Command0_Click()
Dim oConn As New ADODB.Connection

oConn.Open ("myDatabase")
myExecutionString = "SP_StatusUpdate " & Me.recID
oConn.Execute (myExecutionString)
oConn.Close
End Sub

It'd be great if i could simply reference something from the stored procedure in my form when executed.

Completely stuck so any good ideas appreciated

cheers

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[bitmite]

send in a paramieter and declare it ByRef

ie
Funtion test(ByRef Name as String)
.
.
.
End Function

then set Name equal to what ever in the function

when you call it

dim name as string
test(name)

name will then have the value passed to it by reference and you can you it

Brought to you By Nedaineum
The Portal to Geek-tum

 

[Axoliien]

Do you have something declared as an output variable? You can run your stored procedure and it will automatically return recordsets, but you should declare your return variable as an OUTPUT type. Could also just be I have never used the RETURN keyword. Does it exist?

CREATE PROCEDURE upProjTrack_AddProjects
    @ProjTitle VARCHAR(50),
    @ProjIdent INT OUTPUT

AS

    INSERT INTO tblProjectInfo
    (
      Title
    )
    VALUES
    (
      @ProjTitle,
    )

SELECT @ProjIdent = SCOPE_IDENTITY()
GO