Getting the Stored Procedure Value

[ryan]

So heres the deal. I'm currently running a few stored procedures that take in some paramaters to execute some stuff, all is well and working. The problem is that this one new stored procedure I'm creating needs to return an output value, which I am, but I need to capture that value in ASP. I currently am running the procedure using an ADODB.Command object. I'm not sure what I do so I can capture that returned value though, any help.

I thought I saw somewhere that you specify the parameter you want to return a value as returnable or something if that rings a bell or anything ;-).

Thanks,
Ryan

 

[pgferro]

post the sp

 

[ryan]

Here's some snipits:

CREATE PROC CreateAccount
...
AS

DECLARE @WebAccountID INT

BEGIN TRAN

SELECT @WebAccountID =(SELECT TOP 1 WebAccountID FROM WebAccounts WHERE WebServerID <> (SELECT WebServerID from WebAccounts, Accounts WHERE Accounts.WebAccountID = WebAccounts.WebAccountID AND Accounts.AccountID = (SELECT MAX(AccountID) FROM Accounts) ) AND WebAccounts.Status = 0)
UPDATE WebAccounts SET Status = 1 WHERE WebAccountID = @WebAccountID

COMMIT TRAN

SELECT @WebAccountID AS 'WebAccountID'

 

[cdidion]

To get a return parameter from a stored procedure you must include it in the parameter list for the command object that makes the call - using a return value parameter when creating it:

With oCmd
.NamedParameters = True
.CommandType = adCmdStoredProc
.CommandText = &quot;uspSetAlarmDef&quot;
.Parameters.Append .CreateParameter(&quot;@RETURN_VALUE&quot;, _
adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter(&quot;xsReturnString&quot;, _
adVarChar, 40, adParamReturnValue)
.Parameters.Append .CreateParameter(&quot;@xuProcGrpUID&quot;, _
adGUID, adParamInput, , msProcGrpUID)
.Parameters.Append .CreateParameter(&quot;@xiCode&quot;, adInteger, _
adParamInput, , mlCode)
.ActiveConnection = oConn
.Execute
End With

Then after the call is complete you can go to the parameters collection of the command object and get the value of the one you want:

sReturnValue = oCmd.Parameters(&quot;xsReturnString&quot.Value

Sepcial Note: The stored proc call I did above uses the new NamedParameters property (new to ADO 2.6 I think). This means that I have to define all the parameters of the stored proc, but I only go to the database once. There is another way - by setting the ActiveConnection property first then calling .Parameters.Refresh, the parameters collection is populated from the stored proc for you, but requires an extra hit on the database when doing so. However, you can then just set and read the values of the parameters and then execute then command.

Hope this helps. -Chris Didion
Matrix Automation, MCP

 

[ryan]

It's not the whole SP but at least the part that needs to be returned ... the @WebAccountID. ;-)