Return a Value from a Stored Procedure

[MichaelaLee]

Hi Everyone,
I need to get a new Id from a stored procedure that just added a new record. How can I go about getting the returned value back. After this insert is done I need to use that ID for other procs in my procedure. Thanks Here is an example of what I doing without returning a value.:

CREATE PROCEDURE [dbo].[nf_AddGroupFac]
@FirstName nvarchar(25),
@LastName nvarchar(25),
@RETURN_VALUE int output
AS

Declare @LastValue int

Set @LastValue = (Select LastId from Group_Fac_Support)
set @LastValue = @LastValue + 1
Update Group_Fac_Support
Set LastId = @LastValue
select @RETURN_VALUE = @LastValue

Insert into Group_Fac(G_FACID, FirstName, LastName)
Values(@LastValue, @FirstName,@LastName)
GO


@LastValue is the value I need to return. I've tried to use the output parameter in ADO, but have not had much luck yet. I would like to get that ID back into my program to use to execute a few other procs. Thanks for any help.

 

[NoCoolHandle]

I have found that you MUST specify the CommandType Parameter of an ADODB Command object

I think the answer is .CommandType=adStoredProc

but the intellisence in VB should prompt you.

e.g.(from memory so please excuse any mistakes - they should be just in the order and type os some of the parameters)

dim cm as command
with cm
  .commandtext ="yourPRoc"
  .commandtype=adStoredProc
  .parameters.add .createParameter("@someoutput",int,4,adOutput)
.activeconnection=cn
cn.open
.execute
msgbox .Parameters("@someoutput")
cn.close
end with