get a return value from a stored procedure in ASP

[gvision]

Hi everybody,
Could someone help me ???
I don't know how to get a return value from a stored procedure using ASP. Could someone tell me how to do that?

Here's my stored procedure :

CREATE PROCEDURE dbo.sp_getNewAgrementNumber(@category nvarchar(20), @newRecord bit, @idRecord int) AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET LOCK_TIMEOUT 3000

BEGIN TRAN

DECLARE
@newId bigint,
@oldAgrementNum bigint,
@newAgrementNum nvarchar(20)

If @category = 'R'
BEGIN
SELECT @oldAgrementNum=idAgrement FROM numberTable WHERE typeAgrement = 'R'
SET @newId = @oldAgrementNum
SET @newAgrementNum = @category + CONVERT(nvarchar(20), @newId)

If @newRecord = 0
UPDATE reparateurs SET NUMERO = @newAgrementNum WHERE ID = @idRecord
else if @newRecord = 1
INSERT INTO reparateurs (NUMERO) VALUES(@newAgrementNum)

SET @newId = @newId + 1
UPDATE numberTable SET idAgrement = @newId WHERE typeAgrement = 'R'
END
else
BEGIN
SELECT @oldAgrementNum=idAgrement FROM numberTable WHERE typeAgrement = 'L'
SET @newId = @oldAgrementNum
SET @newAgrementNum = @category + CONVERT(nvarchar(20), @newId)

If @newRecord = 0
UPDATE reparateurs SET NUMERO = @newAgrementNum WHERE ID = @idRecord
else if @newRecord = 1
INSERT INTO reparateurs (NUMERO) VALUES(@newAgrementNum)

SET @newId = @newId + 1
UPDATE numberTable SET idAgrement = @newId WHERE typeAgrement = 'L'


END

SELECT newAgrement = @newAgrementNum


COMMIT TRAN
GO

Thank you

 

[Falcon99]

Here's what I do:


sSQL = "sp_getNewAgrementNumber category, newRecord, idRecord"


Set rsRetSP = Server.CreateObject ("ADODB.Recordset&quot
Set rsRetSP = objConn.Execute(sSQL)

inewAgrement = rsRetSP("newAgrement&quot

The object is to excute the procedure like it going to return a recordset. Then reference the returned value as a "field".

There might be other ways.

HTH

 

[gvision]

I tried this but I got this error message :
Item cannot be found in the collection corresponding to the requested name or ordinal.

 

[Falcon99]

BTW, I'm using SQL2K.

The only other difference I see is that my select statement is:

SELECT @newAgrementNum AS newAgrement

Do you get a return value when runnning in Query Analyzer?

 

[gvision]

Yes I do get a return value when running Query Analyzer.
I'm also using sql 2K

 

[Falcon99]

Sorry, typo on my part.

sSQL = "execute sp_getNewAgrementNumber category, newRecord, idRecord"

 

[gvision]

still doesn't work