ADODB在具有可选输入参数的输出参数上失败吗?

I have a stored procedure in a SQL Server 2008 R2 database with an optional input parameter and an output parameter like this:

我在SQL Server 2008 R2数据库中有一个存储过程，它有一个可选的输入参数和输出参数，如下所示:

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:

当我在SSMS中不使用可选参数调用存储过程时，它工作得很好:

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:

但是当我在ASP经典中通过ADODB尝试同样的事情时

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:

它什么都不做，@TicketID是空的，还有ADODB。连接包含这个NativeError 8162:

"The formal parameter \"@UserID\" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output."

“正式参数\”@UserID\“不是作为输出参数声明的，而是通过请求输出传递的实际参数。”

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?

我使用ADODB来调用存储过程，而不需要任何可选参数。这是ADODB中已知的缺陷吗?还是有输出参数时，我需要做一些特别的事情来使它工作?

1 个解决方案

#1

Turns out the ADODB default is to ignore the parameter names and simply pass them to the sproc in the order appended.

原来ADODB的默认值是忽略参数名，然后按照附加的顺序将它们传递给sproc。

Hence, in the example given, @Ticket was passed to the sproc as @EmailAddress, @UserID was passed as @Ticket, and no output parameter was passed at all!

因此，在给定的示例中，@Ticket作为@EmailAddress传递给sproc， @UserID作为@Ticket传递给@UserID，根本没有传递输出参数!

If you want ADODB to match parameters by name you have to set the ADODB.Command .NamedParameters = True.

如果希望ADODB按名称匹配参数，则必须设置ADODB。命令.NamedParameters = True。

#1