两次使用相同的参数 [英] Using the same parameter twice

问题描述

我有以下SQL查询：

SELECT SUM(OpenInterest) *(SELECT DISTINCT Future
                           FROM MTM
                           WHERE Expiry = [dbo].fx_GetRelativeExpiry(@date, 1, @Code)
                             and TradeDate = @date
                             and Code = @Code
                             and type = @Type
                             and Class = 'Foreign Exchange Future') / 1000
FROM MTM
WHERE Expiry = [dbo].fx_GetRelativeExpiry(@date, @N, @Code)
  and TradeDate = @date
  and Code = @Code
  and type = @Type
  and Class = 'Foreign Exchange Future'

我想在Excel中用作功能。问题是我在上述查询中多次重用了参数，而且我不知道如何在excel中做到这一点而不创建新的（基本上是多余的）参数。这是我的VBA代码：

Which I want to use as a function in Excel. The issue is that I reuse parameters many times in the above query and I don't know how to do that in excel without creating a new (and basically redundant) parameter. This is my VBA code:

Function GetTotalOI(TradeDate As Date, Code As String, OptionType As String, N As Integer) As Variant

    'Create and open the connection
    Dim oConnection As Connection
    Set oConnection = New Connection
    oConnection.ConnectionString = strConnectionStringYieldX
    oConnection.Open

    'Create the command object
    Dim oCommand As Command
    Set oCommand = New Command
    oCommand.CommandType = adCmdText

    Dim SQLString As String

    SQLString = "SELECT SUM(OpenInterest) * (SELECT DISTINCT Future" _
    & "                                      FROM MTM" _
    & "                                      WHERE Expiry = [dbo].fx_GetRelativeExpiry(?, 1, ?)" _
    & "                                        and TradeDate = ?" _
    & "                                        and Code = ?" _
    & "                                        and type = ?" _
    & "                                        and Class = 'Foreign Exchange Future') / 1000" _
    & "          FROM MTM" _
    & "          WHERE Expiry = [dbo].fx_GetRelativeExpiry(?, ?, ?)" _
    & "            and TradeDate = ?" _
    & "            and Code = ?" _
    & "            and type = ?" _
    & "            and Class = 'Foreign Exchange Future'"

    oCommand.CommandText = SQLString
    oCommand.ActiveConnection = oConnection

    oCommand.Parameters.Append oCommand.CreateParameter("Date1a", adDBTimeStamp, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("Code1a", adVarChar, adParamInput, 50)
    oCommand.Parameters.Append oCommand.CreateParameter("Date2a", adDBTimeStamp, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("Code2a", adVarChar, adParamInput, 50)
    oCommand.Parameters.Append oCommand.CreateParameter("Typea", adVarChar, adParamInput, 1)
    oCommand.Parameters.Append oCommand.CreateParameter("Date1", adDBTimeStamp, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("N", adInteger, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("Code1", adVarChar, adParamInput, 50)
    oCommand.Parameters.Append oCommand.CreateParameter("Date2", adDBTimeStamp, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("Code2", adVarChar, adParamInput, 50)
    oCommand.Parameters.Append oCommand.CreateParameter("Type", adVarChar, adParamInput, 1)

    oCommand.Parameters.Item("Date1a").Value = TradeDate
    oCommand.Parameters.Item("Code1a").Value = Code
    oCommand.Parameters.Item("Date2a").Value = TradeDate
    oCommand.Parameters.Item("Code2a").Value = Code
    oCommand.Parameters.Item("Typea").Value = OptionType
    oCommand.Parameters.Item("Date1").Value = TradeDate
    oCommand.Parameters.Item("Code1").Value = Code
    oCommand.Parameters.Item("N").Value = N
    oCommand.Parameters.Item("Date2").Value = TradeDate
    oCommand.Parameters.Item("Code2").Value = Code
    oCommand.Parameters.Item("Type").Value = OptionType

    Dim result As New ADODB.Recordset
    Set result = oCommand.Execute

    Dim resultA As Variant
    GetTotalOI = WorksheetFunction.Transpose(result.GetRows)

    oConnection.Close

End Function

代码可以正常工作，但是很混乱。我只需要4个参数。知道怎么做吗？就像有一种方法可以通过名称指定参数，而不是像查询字符串中的？一样吗？

The code works, but it is a mess. I only need 4 parameters. Any idea how to do it? Like is there a way to specify parameters by name instead of just as ? in the query string?

我的连接字符串看起来像这样：

My connection string looks like this:

Const strConnectionStringYieldX As String = "Provider=SQLNCLI10.1;Data Source=xxxx;Initial Catalog=xxxx;Uid=xxxx;Pwd=xxxx;"

编辑

为澄清这个问题，在ADO中，您必须将参数指定为？，而不是 @ParamName 之类的参数。如果您两次使用相同的参数，则必须在代码中重新创建该参数。这是丑陋和令人不愉快的。因此，在此查询中，我实际上仅使用4个参数，因为要重复很多次，所以我必须唯一地命名并创建11个参数。因此，如果您阅读了VBA代码，您将看到我有名为 date1a ， date2a ， date1 和 date2 -但这些都是相同的日期！我敢肯定，有一种本地方法可以在查询中使用某种命名参数，因此只需声明4个参数即可。

To clarify the question, in ADO you have to specify parameters as ? rather than something like @ParamName which means if you use the same parameter twice, you have to recreate the parameter in your code. Which is ugly and unpleasant. So in this query where I really only use 4 parameters, because I repeat them a lot I have to uniquely name and create 11 parameters. So if you read the vba code you'll see I have parameters named date1a, date2a, date1 and date2 - BUT THESE ARE ALL THE SAME DATE! I am certain there is a native way to used some sort of named parameter in the query and thus only have to declare the 4 parameters.

推荐答案

我确定有适当的方法来执行此操作，但最后我只是在数据库上创建了一个UDF，它仅允许我使用4个参数以及某些否则无法正常工作的T-SQL命令和过程。但是，如果有人知道合适的替代方法，请将其发布！

I'm sure there is a proper way to do this but in the end I just made a UDF on the DB which allows me to use only 4 parameters and also certain T-SQL commands and procedures that otherwise wouldn't work. But if someone knows of a suitable alternative, please post it!

这篇关于两次使用相同的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！