vba 使用多个参数从 Excel 调用存储过程

Calling a Stored Procedure from Excel with multiple parameters

提问by Carla Van Blake

I have set up a connection to my SQL server to the database where the stored procedure is located. The Stored Procedure works fine in SQLServer. The Stored Procedure runs from the connection with hard coded parameters in Excel just fine. I get my dataset and it is inserted into my spreadsheet. The macro does not work. In the macro, I am trying to point to a range in the spreadsheet that contains my parameter values so I can type the values into the spreadsheet and then have the macro pick them up and pass them to the stored procedure. I am using date formats for both the spreadsheet values and the parameters in the Stored Procedure. I want the dataset returned to be updated in the spreadsheet with the new data when the macro runs. Here is my Macro:

我已经建立了到我的 SQL 服务器到存储过程所在数据库的连接。存储过程在 SQLServer 中运行良好。存储过程通过与 Excel 中的硬编码参数的连接运行就好了。我得到了我的数据集并将其插入到我的电子表格中。宏不起作用。在宏中，我试图指向电子表格中包含我的参数值的范围，以便我可以在电子表格中键入这些值，然后让宏选取它们并将它们传递给存储过程。我对电子表格值和存储过程中的参数都使用日期格式。我希望在宏运行时使用新数据在电子表格中更新返回的数据集。这是我的宏：

Sub GetHeatMapData()

    With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "" & Range("B9").Value & "'"

    End With
    ActiveWorkbook.Connections("CARLA-PC-Billing-SP").Refresh

End Sub

However if I try to run the Stored Procedure from a macro in Excel, one of two things happens:

但是，如果我尝试从 Excel 中的宏运行存储过程，则会发生以下两种情况之一：

1. If there is an existing dataset in the spreadsheet that was created running the stored procedure from the connection window, then the macro runs without errors but it is not picking up the dynamic variables so the data does not change as it should.

2. If I delete the data set created by running the Stored Procedure from the connection window, select the cell where the data should start, then fire the macro I get a 'subscript out of range' error and nothing happens.

1. 如果在从连接窗口运行存储过程而创建的电子表格中存在现有数据集，则宏运行时不会出错，但不会获取动态变量，因此数据不会按原样更改。

2. 如果我删除通过从连接窗口运行存储过程创建的数据集，选择数据应该开始的单元格，然后触发宏，我收到“下标超出范围”错误，但没有任何反应。

I am setting NOCOUNTto off at the end of my Stored Procedure. Here are the parameter definitions in the Stored Procedure:

我NOCOUNT在存储过程结束时设置为 off。以下是存储过程中的参数定义：

-- Add the parameters for the stored procedure here
    @StartDate Date, 
    @EndDate Date

Here are my connection settings:

这是我的连接设置：

My question is why is stored procedure not getting my parameters from the Excel spreadsheet cells and using them to filter the returned data?

我的问题是为什么存储过程没有从 Excel 电子表格单元格中获取我的参数并使用它们来过滤返回的数据？

回答by guest2

1 – expose parametars in excel

1 – 在 excel 中公开参数

2 – define that parameters are entered from excel

2 – 定义从excel输入参数

3 – OK and refresh.

3 – 确定并刷新。

OK, You don't have to enter parameters in the command text, you can define command text in a way to expect parameters to be passed to the store procedure from excel cell. This is useful especially if your country don't use US data format. Also, it enables end user to make refresh with some other parameters without need to edit command text. It is simple – in the excel that have connection to sql server in command text after store procedures name you enter ? – as many ? as you have different parameters that your store procedure expects (divided by ,) – like this - execute dbo.your_procedure ?,? After that, you go in the parameters tab (on the same form that you entered command text) and define from witch cell witch parameter is passed to store procedure. Of course, in the stored procedure also need to be specified what parameters are expected:

好的，您不必在命令文本中输入参数，您可以以期望参数从 Excel 单元格传递到存储过程的方式定义命令文本。这在您所在的国家/地区不使用美国数据格式时尤其有用。此外，它使最终用户能够使用其他一些参数进行刷新，而无需编辑命令文本。这很简单 - 在您输入的存储过程名称之后的命令文本中连接到 sql server 的 excel 中？- 尽可能多 ？因为你有你的存储过程期望的不同参数（除以，） - 像这样 - 执行 dbo.your_procedure ?,? 之后，您进入参数选项卡（在您输入命令文本的同一表单上）并定义从女巫单元格女巫参数传递到存储过程。当然，

CREATE procedure [dbo].[your_procedure]

创建过程 [dbo].[your_procedure]

( @DateFrom datetime, @DateTo datetime ) As

（@DateFrom 日期时间，@DateTo 日期时间）作为

--- your store procedure ---

--- 您的存储过程 ---

In excel – parameter 1 will be send to the store procedure to a DateFrom parametar.

在 excel 中 - 参数 1 将发送到存储过程的 DateFrom 参数。

回答by JNevill

When you execute a stored procedure, the statement generally looks like:

执行存储过程时，语句通常如下所示：

EXECUTE myProc('param1', 'param2')

The way your command will expand will come out like:

您的命令将展开的方式将如下所示：

EXECUTE myProc 'param1param2`

Which is nonsense. Try instead:

这是胡说八道。试试吧：

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & Range("A9").Value & "','" & Range("B9").Value & "');"

You may still run into issues with the date formatting, so you can handle that in VBA too:

您可能仍会遇到日期格式问题，因此您也可以在 VBA 中处理：

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"

Lastly, I find it good practice to send dynamically generated sql to a variable, and print it to the debug/immediate window before executing so I can catch stuff like that.

最后，我发现将动态生成的 sql 发送到变量，并在执行之前将其打印到调试/立即窗口是一种很好的做法，这样我就可以捕获类似的东西。

sqlStatement = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"
debug.print sqlStatement
With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = sqlStatement

And now you'll have your statement in your immediate window, which you can copy and paste into a SQL client to execute independently of your code.

现在您将在您的即时窗口中看到您的语句，您可以将其复制并粘贴到 SQL 客户端中以独立于您的代码执行。

回答by Mike Miller

You're missing that comma

你错过了那个逗号

"EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "','" & Range("B9").Value & "'"

"EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "','" & Range("B9").Value & "'"