0

I am trying to write a report query that prompts the user of the report to enter an array of values. I tried using something like:

SELECT        class_style_view.Course_Code
FROM          class_style_view  class_style_view_1.Course_Code
WHERE        (class_style_view.Course_Code IN (=@PromptedArray)

I am not sure how I could/should be writing this. I want the prompt to come up for my report and be able to put in something like this: ('41229', '65002', '65435', '64059') so I can query all the items in the table with a variable set of Course_Code values.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
Zackery Reichenbach-Carr
  • 3
  • 2

1 Answers1

1

The usual method for this is to first create a dataset that includes all the potential options for the user. For your example, this might be:

SELECT DISTINCT class_style_view.Course_Code FROM class_style_view

Then set up a parameter to prompt the user. Make sure to set the parameter to "Allow Multiple Values." Set the Available Values to come from a query and select the dataset you just created.

Now you can set up a query that uses that parameter:

SELECT * FROM class_style_view where Course_Code in ( @MyParameterName )

Without giving a drop down to the user, I'm not sure if you can get the parameters they enter directly into an array. You could parse the string they enter to separate it on commas, but giving them the dropdown is easy and a decent UI.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • The dropdown would have over 90,000 items listed. I am not sure how to parse out the string. – Zackery Reichenbach-Carr Apr 13 '13 at 18:42
  • There are many ways to parse a comma separated string in SQL, such as http://stackoverflow.com/questions/5738834/how-to-parse-a-comma-delimited-string-of-numbers-into-a-temporary-orderid-table but if you can break the 90K items down, then you could give a couple of dropdowns, maybe the first allows them to select a department or date range, or class prefix, and then a second dropdown for the possible options. – Jamie F Apr 14 '13 at 01:04