ADODB是否会对带有可选输入参数的输出参数失败？ [英] Does ADODB fail on output parameters with optional input parameters?

问题描述

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

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

当我在SSMS中调用不带可选参数的存储过程时，效果很好：

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

但是当我在ASP Classic中通过ADODB尝试相同的操作时，如下所示： / p>

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")

不执行任何操作，只剩下 @TicketID 为空，并且ADODB.Connection包含此NativeError 8162：

it does nothing, @TicketID is left Empty, and the ADODB.Connection contains this NativeError 8162:

形式参数\ @ UserID\未声明为OUTPUT
参数，但实际参数已传递到请求的输出中。

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

我一直使用ADODB来调用没有可选参数的存储过程。这是ADODB中的已知缺陷，还是我需要做一些特殊的事情才能使它在有输出参数的情况下起作用？

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的默认设置是忽略参数名称，只是将它们按附加顺序传递给存储过程。

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

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

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!

如果您希望ADODB匹配参数名称必须设置ADODB.Command .NamedParameters = True 。

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

这篇关于ADODB是否会对带有可选输入参数的输出参数失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！