Using the same parameter twice

Question

I have the following SQL query:

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'

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

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

EDIT

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.

Answer 1

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!