how to pass a return value from a sql 2000 stored procedure into access vba routine

[Keith G Hicks]

I need to have a SQL 2000 stored procedure pass an output parameter back to
my MS Access code. I've got the sp all set up but how do I execute it and
get the value back in vba?
I just need to pass a couple of parameters to the sp so that the select in
the sp will return the correct record and then I need to pass back a
particular field's value of that record.

Here's my sp so far:

CREATE PROCEDURE spE_GetAssetRec
@MoveDirection as char(1),
@UnitNum as Int,
@CatFilter as varchar(30),
@ReturnUnitNum as Int Output

AS
Set NoCount On

Declare
@Sql as varchar(8000)


If @CatFilter = ''
Select @CatFilter = ''
else
Select @CatFilter = ' And AssetCategoryCode = ''' + @CatFilter + ''''

If @MoveDirection = 'N'
begin
Select @Sql = 'Select Top 2 UnitNumber From tEAssets Where UnitNumber >= '
+ str(@UnitNum) + @CatFilter + ' Order By UnitNumber'

EXEC ( 'Declare Cur_Assets Insensitive cursor for ' + @Sql)
Open Cur_Assets

-- go to the NEXT unit number
Fetch Next from Cur_Assets INTO @ReturnUnitNum

-- Still need to account for end of file

end
--exec(@sql)
Return @ReturnUnitNum

Close Cur_Assets
Deallocate Cur_Assets
GO


Again, I just need code to run the sp from Acess and get the return value
into a variable in vba.


Thanks,

Keith

 

[Vadim Rapp]

KG> I need to have a SQL 2000 stored procedure pass an
KG> output parameter back to my MS Access code.

For an s.p like this

create PROCEDURE sptest (@p1 int) AS return @p1*2

the VBA code would be

Dim c As New ADODB.Command, Result As Integer
c.CommandText = "{? = CALL dbo.sptest(?) }"
c.ActiveConnection = CurrentProject.Connection
c.Parameters.Refresh
c.Parameters("@p1") = 3
c.Execute
Result = c.Parameters("@return_value")

Note that we didn't declare the output parameter in the s.p.


Though it would be easier to implement as scalar function:

create FUNCTION dbo.Function1 (@p1 int) RETURNS int AS
BEGIN
RETURN @p1*2
END

then the code would be

Result = CurrentProject.Connection.Execute("select dbo.function1(4)")(0)


Vadim

 

[Jay]

I'm trying to implement this, but when I hit the line:'

Result = c.Parameters("@return_value")

I get the error "Item cannot be found in the collection
corresponding to the requested name or ordinal"

What am I missing?

Jay