0

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.

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

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.

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.

Many thanks in advance.

Josh D
  • 107
  • 1
  • 3
  • 9

1 Answers1

2

It sounds like you want to pass a list of Account Numbers and Active Agreements which will be used in your 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).

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

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.

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

Community
  • 1
  • 1
CodingQuant
  • 310
  • 2
  • 7
  • This message led me on the right path. I created a function to split the string and trimmed the results into a new table which I used for my stored procedure. Thank you very much for helping me think it through! – Josh D Jan 23 '14 at 22:11