how can i get a stored procedure to return a value

[ryan]

my stored procedure doesn't require any input but returns two values one called w3serviceid and the other called w3webid. so far i get an error saying 'Procedure GetW3ServiceID has no parameters and arguments were supplied' what the heck. why is this happening? so far my asp command looks like this:

objCommand.CommandType=adCmdStoredProc
objCommand.Parameters.Append objCommand.CreateParameter("w3serviceid",adInteger,adParamOutput,3)
objCommand.CommandText="getw3serviceid"
objCommand.Execute

HELP PLEASE ... I'M DESPERATE!!!!!!!!!!!!!!!!

 

[ryan]

here is the stored procedure ...


CREATE procedure GetW3ServiceID as begin
declare @w3serviceid int
declare @w3webid int
begin tran
select @w3serviceid=(select top 1w3serviceid from w3services where status=0)
select @w3webid=(select w3webid from w3services where w3serviceid=@w3serviceid)
update w3services set status=1 where w3serviceid=@w3serviceid
commit tran
select @w3serviceid as 'w3serviceid', @w3webid as 'w3webid'

End

 

[palbano]

I'm not going to test this theory out so it may be wrong but... it seems that you need to catch the returned 'Recordset' object to get access to the values.

> objCommand.Execute

rs = objCommand.Execute

What do you think?

-pete

 

[ryan]

i found the answer already ... i was an issue with my stored procedure ... thanks though!

 

[culshaja]

The preferred way to do this is to declare a parameter with the OUTPUT keyword after it.
Is the value you want to return an IDENTITY value?

The header should look as follows:

CREATE PROC getw3serviceid (@w3serviceid int OUTPUT)
AS
   ....

This will then return the value into the parameter.
You need to remember that the operations are asynchronous and if you are using client-side cursor location on the connection then you will have to write an empty loop checking the parameter using isNothing.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305