Mam następujące zapytanie 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'

Które chcę użyć jako funkcji w Excelu. Problem polega na tym, że wielokrotnie używam parametrów w powyższym zapytaniu i nie wiem, jak to zrobić w programie Excel bez tworzenia nowego (i zasadniczo zbędnego) parametru. To jest mój kod VBA:

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

Kod działa, ale jest bałagan. Potrzebuję tylko 4 parametrów. Masz jakiś pomysł, jak to zrobić? Podobnie jak istnieje sposób na określenie parametrów według nazwy zamiast tak? w ciągu zapytania?

Mój ciąg połączenia wygląda tak:

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

EDYTOWAĆ

Aby wyjaśnić pytanie, w ADO musisz określić parametry jako? raczej niż coś takiego@ParamName co oznacza, że ​​jeśli użyjesz tego samego parametru dwa razy, musisz odtworzyć parametr w kodzie. Co jest brzydkie i nieprzyjemne. Więc w tym zapytaniu, gdzie naprawdę używam tylko 4 parametrów, ponieważ często je powtarzam, muszę jednoznacznie nazwać i utworzyć 11 parametrów. Więc jeśli przeczytasz kod vba, zobaczysz, że mam nazwy parametrówdate1a, date2a, date1 idate2 - ALE TO SĄ WSZYSTKIE SAME DATY! Jestem pewien, że istnieje natywny sposób na użycie pewnego rodzaju nazwanego parametru w zapytaniu, a zatem musi tylko zadeklarować 4 parametry.