No question because I searched the FAQ for array parameters!! 1

[RRinTetons]

And found EXACTLY what I needed; it doesn't always work quite that well, but in this case it was 100% spot on. I can now emulate an array variable in an argument passed to a SQL stored procedure to provide the user with the ease to enter an arbitrary number of values to query for.

Between that and being able to extract values from an Excel spreadsheet to bind to a SQL query's parameters I have ascended to near mythical status among the users.

Of course, that doesn't mean much, but it's fun anyhow...





-
Richard Ray
Jackson Hole Mountain Resort

 

[tperri]

So are you going to share your ephiphany or just brag?

 

[AlexCuse]

I for one am very glad you checked the FAQ's. This is truly one of the most frequently asked questions in the forum.

Hopefully your sharing will encourage others to do the same.

http://www.tek-tips.com/faq.cfm?pid=183

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[RRinTetons]

I guess I just assumed that everybody who accesses this resource more often then I already knew about it! Google or search here for 'fn_Split'. I you track down the original article you'll find example code to make it work.

Getting Microsoft Query to look at cell references for query parms is a matter of entering the call to the SP in a very specific way. In the SQL window enter

{Call spName(?,?,?)}

The curly braces are required, as is the Call... syntax rather than execute or just the bare name. MS Query will complain about not being able to represent the query, then ask for a value for the parameter(s). Just leave them blankl and click OK. Now tell MS Query to return the (non-existent) data to Excel. Back in Excel you have the choice to rename the query prompt, supply a default value or enter a cell reference. You can also tell Excel to refresh the query whenever the cell reference's value changes.

Now let the user enter as many values as they want in a range of cells, use a formula to concat them into a single string and use a ref to that concated string as the value passed to the query. In the sp use fn_Split to turn the delimited string into a table valued variable, then do a select for non-blank values and you've got the SELECT statement for the IN part of the WHERE clause.

I know, it's a funny thing to get so excited about, but it all comes together to solve a huge backlog of user requests so it makes me happy!


-
Richard Ray
Jackson Hole Mountain Resort

 

[AlexCuse]

I know, it's a funny thing to get so excited about

Most of the things we tend to get excited about are (at least my special lady says so ;-) )

Thanks for sharing.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page