October 2, 2002 at 11:03 am
I'm fairly new to stored procedures, so please bare with me. I need some help with the following stored procedure. It uses an OUTPUT parameter, but I can't get it to return the value. Is my code incorret?
Here is the code in my ASP page:
With objCmd
.ActiveConnection = objConn
.CommandText = "spGcoTotalQuotedNew"
.CommandType = adCmdStoredProc
'Add stored procedure parameters
.Parameters(1) = .CreateParameter ("@GcoNewTotalQuote", adInteger, adParamOutput)
'Execute the stored procedure and catch any error code
.Execute
Set intErrorCode = .Parameters(0)
'Extract the output parameter value from procedure
Gco = .Parameters("@GcoNewTotalQuote")
End With
If intErrorCode <> 0 Then
Response.Write (Gco)
Else
Response.Write("fail")
End If
And here is the stored procedure:
CREATE PROCEDURE spGcoTotalQuotedNew
@GcoNewTotalQuote int OUTPUT
AS
IF EXISTS
(SELECT CustomerStatus, COUNT(uniqueid) AS Expr1 FROM RFQ WHERE (Quotelocation = N'GCO') GROUP BY CustomerStatus HAVING (CustomerStatus = N'New'))
RETURN (@GcoNewTotalQuote)
ELSE
RETURN(0)
GO
I am having problems with assigning the return value to my variable. help!!!
Thanks
October 2, 2002 at 3:33 pm
I don't see where you are setting your output variable? Have you tested it in QA to make sure you are getting the return you expect? Can you explain what you expect to happen so you get a proper response?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 3, 2002 at 5:22 am
Antares is right. Have you posted the whole of the Stored Procedure code?
You'll need something like SET @GcoNewTotalQuote = ... somewhere in your code. Also just setting the output parameter in this way is all that's needed (similar to using a ByRef parameter in VB) The keyword RETURN terminates processing of the Procedure at the point it's issued and is effectively the reurn value of the procedure (normally 0 if the procedure completes succesfully)
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply