两次使用相同的参数

本文介绍了两次使用相同的参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下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!