使用多个参数从Excel调用存储过程 [英] Calling a Stored Procedure from Excel with multiple parameters

问题描述

我已经建立了到存储过程所在数据库的SQL Server连接.该存储过程在SQLServer中可以正常工作.从Excel中带有硬编码参数的连接运行存储过程就可以了.我得到了数据集并将其插入到电子表格中.宏不起作用.在宏中，我试图指向电子表格中包含我的参数值的范围，以便我可以在电子表格中键入这些值，然后让宏将其拾取并将其传递给存储过程.我将电子表格值和存储过程中的参数都使用日期格式.我希望在宏运行时使用新数据在电子表格中更新返回的数据集.这是我的宏:

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:

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

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

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

1. 如果在电子表格中存在一个从连接窗口运行存储过程的现有数据集，则该宏运行无误，但它没有拾取动态变量，因此数据不会随其改变应该.

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.

如果我从连接窗口中删除通过运行存储过程"而创建的数据集，请选择应从其开始数据的单元格，然后触发宏，我将收到下标超出范围"错误，并且什么也没有发生.

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.

在存储过程结束时，我将NOCOUNT设置为关闭.这是存储过程中的参数定义:

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

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

这是我的连接设置:

Here are my connection settings:

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

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

推荐答案

1 –在excel中公开参数

1 – expose parametars in excel

2 –定义从excel输入参数

2 – define that parameters are entered from excel

3 –确定并刷新.

好的， 您不必在命令文本中输入参数，可以以一种期望将参数从excel单元传递到存储过程的方式定义命令文本.这在您的国家/地区不使用美国数据格式的情况下尤其有用.此外，它使最终用户可以使用其他一些参数进行刷新，而无需编辑命令文本. 这很简单–在Excel中，您输入的存储过程名称后在命令文本中已连接到sql server. - 尽可能多 ?因为您具有存储过程期望的不同参数(除以)(像这样)，请执行dbo.your_procedure?，?. 之后，进入参数选项卡(采用与输入命令文本相同的形式)，并从女巫单元中定义女巫参数传递给存储过程.当然，在存储过程中还需要指定期望的参数:

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:

创建过程[dbo].[您的过程]

CREATE procedure [dbo].[your_procedure]

( @DateFrom datetime， @DateTo日期时间 ) 为

( @DateFrom datetime, @DateTo datetime ) As

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

--- your store procedure ---

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

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

这篇关于使用多个参数从Excel调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！