I have a stored procedure in a SQL Server 2008 R2 database with an optional input parameter and an output parameter like this:
CREATE PROCEDURE [dbo].[spCreateTicket]
(
@TrackingCode varchar(25),
@EmailAddress varchar(250) = null,
@Ticket varchar(1000),
@UserID int,
@TicketID int output
)
AS
SET NOCOUNT ON
INSERT INTO dbTicket (TrackingCode, EmailAddress, Ticket, UserID)
SELECT
@TrackingCode, @EmailAddress, @Ticket, @UserID
SELECT @TicketID = SCOPE_IDENTITY()
RETURN @TicketID
When I invoke the stored procedure without the optional parameter in SSMS it works fine:
DECLARE @TicketID int
EXEC [dbo].[spCreateTicket]
@TrackingCode = 'xOCDUv289u403k5h24s5869vK',
@Ticket = 'Something broke!',
@UserID = 64307,
@TicketID = @TicketID OUTPUT
But when I try the same thing through ADODB in ASP classic like this:
SET cmd = Server.CreateObject ("ADODB.Command")
cmd.ActiveConnection = HelpDeskConnection
cmd.CommandText = "spCreateTicket"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter ("@TrackingCode",adVarChar,adParamInput,25,RandomString(25))
cmd.Parameters.Append cmd.CreateParameter ("@Ticket",adVarChar,adParamInput,1000,Ticket)
cmd.Parameters.Append cmd.CreateParameter ("@UserID",adInteger,adParamInput, ,Session("UserID"))
cmd.Parameters.Append cmd.CreateParameter ("@TicketID",adInteger,adParamOutput)
cmd.Execute
TicketID = cmd.Parameters("@TicketID")
it does nothing, @TicketID is left Empty, and the ADODB.Connection contains this NativeError 8162:
"The formal parameter \"@UserID\" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output."
I use ADODB to call stored procedures without optional parameters all the time. Is this a known defect in ADODB, or do I need to do something particular to get it to work when there's an output parameter?
