将参数列表从 Excel 2010 传递到 MS SQL 2012 [英] Passing a Parameter List from Excel 2010 to MS SQL 2012

问题描述

我正在创建一个包含三个输入参数的报告的 .xlsm 文件.输入参数后，最终用户运行一个宏，将参数发送到存储过程，运行存储过程，并将数据显示回 Excel.

I am creating an .xlsm file which contains a report with three input parameters. After inputting the parameters, the end user then runs a macro which sends the parameters to a stored procedure, runs the stored procedure, and displays the data back in Excel.

我的 proc 运行良好，但现在，对于三个参数中的两个(@AccountNumber 和 @ActiveAgreement)，最终用户希望选择为每个参数输入多个值.这是我当前的宏代码:

I have the proc running nicely, but now, for two of the three parameters (@AccountNumber and @ActiveAgreement), the end user would like the option to input more than one value for each. Here is my current macro code:

Sub RefreshQuery()
With ActiveWorkbook.Connections("ImpactDetailSheet").OLEDBConnection
    .CommandText = "DECLARE @PriceList NVARCHAR(10), @AccountNumber NVARCHAR(10), @ActiveAgreement NVARCHAR(10) SET @PriceList = '" & Range("J1").Value & "' SET @AccountNumber = '" & Range("J2").Value & "' SET @ActiveAgreement = '" & Range("J3").Value & "' EXEC [dbo].[Impact] @PriceList, @AccountNumber, @ActiveAgreement"
End With
ActiveWorkbook.Connections("ImpactDetailSheet").Refresh
ActiveWorkbook.Connections("ImpactActiveAgreementSheet").Refresh
ActiveWorkbook.Connections("ImpactKickoutSheet").Refresh
End Sub

我的问题是如何更改代码以包含给定参数的多个值?据我所知，如果我使用的是列表，我将需要更新我的存储过程以在参数周围使用括号(例如 (@AccountNumber)).

My question is how do I alter the code to include more than one value for a given parameter? From what I can tell, I will need to update my stored procedure to use parentheses around the parameters (e.g. (@AccountNumber)) if I'm using a list.

另外，是否可以有 1 个包含多个参数的单元格?例如，用逗号或类似的东西分隔，而不是使用多个单元格作为附加参数.

Also, is it possible to have 1 cell that contains multiple parameters? For example, separated by a comma or something similar, vs. using multiple cells for additional parameters.

非常感谢.

推荐答案

听起来您想传递将在您的存储过程 (SP) 中使用的帐号和活动协议的列表.

It sounds like you want to pass a list of Account Numbers and Active Agreements which will be used in your Stored Proc (SP).

我认为有两种选择:要么为每个参数组合多次执行 Stored Proc，要么更改 SP 以获取列表而不是单个值并处理此列表(如传递数组).

I think there are two options: either you execute the Stored Proc multiple times for each parameter combination, or you change the SP to take a list instead of individual values and process this list (like passing an array).

鉴于你调用SP的方式，我认为后者更合适.

Given the way you are calling the SP, I think the later would be more suitable.

例如，如果您将 SP 更改为以 VARCHAR(MAX) 形式接收 @AccountNumber，则它可能会收到逗号分隔的字符串，例如1005、1234、1754 等可以存储在工作表上的单元格中.然后，您可以在 SP 中拆分此字符串并在查询中使用它.

As an example, if you change the SP to receive @AccountNumber as a VARCHAR(MAX), it could expect to receive a comma separated string, e.g. 1005,1234,1754,etc which could be stored in a cell on the worksheet. You could then split this string in the SP and use it in your query.

有关在 SQL 中拆分字符串的示例，请参阅此处推荐的 SQL Server 2005 的 SplitInts 函数:如何将数组传递给 SQL Server 存储过程

For an example on splitting strings in SQL, see the SplitInts function for SQL Server 2005 recommended here: How to pass an array into a SQL Server stored procedure

这篇关于将参数列表从 Excel 2010 传递到 MS SQL 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！