Getting values back from stored procedures

[oracleguy]

I'm having some problems getting values back from a stored procedure. I know how to put some in but I don't know how to get them back out.

Here is my stored procedure:CREATE PROCEDURE dbo.ext_wwid2
@ShortID nvarchar(10),
@FName nvarchar(50),
@LName nvarchar(50),
@PhoneNum nvarchar(20),
@DA nvarchar(150),
@CID int
AS
declare @WWID int

SELECT @WWID=(min(convert(int,WWID))-1) FROM i_user;

INSERT INTO i_user (ShortID, FirstName, LastName, PhoneNum,
DomainAddress, CID, LongID, WWID)
VALUES(@ShortID, @FName, @LName, @PhoneNum, @DA, @CID,
@LName+', '+@FName, @WWID)

RETURN(@WWID)
GO

Isn't the return method the right way to send it out?

And then how, in ASP, can I get that value? I thought by using the ADO command object but I can't seem to get that to work.

[allida77]

Return exits a sp and all the code beneath will not execute. To return parameters you need to put OUTPUT or OUT(depending on your server) in the parameter declaration :

CREATE PROCEDURE dbo.ext_wwid2
@ShortID nvarchar(10),
@FName nvarchar(50),
@LName nvarchar(50),
@PhoneNum nvarchar(20),
@DA nvarchar(150),
@CID int,
@p_WWID INT OUTPUT
AS

I usually prefix all my parameters with p_ and variables as v_. I am not sure how to call with this with regular asp but just google it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp

[fractalvibes]

Use the ADO command object.
set cmd.CommandText = name of sp
set cmd.CommandType = adStoredProc

You explicitly create and append any parameters
(name, type size, direction, value)
then rs = cmd.Execute
depending upon what you are doing

See www.devguru.com - look in the ADO section and find the
command object.

[oracleguy]

Yeah I found devguru earlier, and I read the part on the command object. However I just can't seem to get it to work. I'll try allida said tomorrow when I get to work.

Someone at work told me it returned the items in a recordset but I couldn't get that to work either.

[fractalvibes]

Looks as though you are just trying to return a single integer value, so would be best to just use an output parameter for that.

Take a look at this article:
http://www.4guysfromrolla.com/webtech/111499-1.shtml

Phil J.