Passing multiple values to SP Parameter 1

There is a recent Article on the BO website that explains how to pass a multi-value parameter to a stored procedure. Thought this would be a neat trick so I tried it. The parameter values get combined correctly (into a single long string) but the SP doesn't seem to know what to do with that value and comes back empty. Anyone else want to try this and see if it really works?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[synapsevampire]

Hi Ken, good to read that you're still amongst us.

The SP will then need to parse out the parameter and constructy the where clause.

I've heard rumors of a new array type for SQL Server, but I've not seen it.

This has always been a problem For coders, and I did test this a while back, and it does work.

This means that you will have to create a character seperated value string to pass, so dates and numerics are more complex to construct.

Here are some examples of SPs:

http://www.sommarskog.se/arrays-in-sql.html

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

There are also functions for most databases that will allow you to do the equivalent of a SPLIT and JOIN for most databases, such as:

http://www.sqlmag.com/articles/index.cfm?articleid=21071&

-k

 

[kenhamady]

I had a vague recollection that this challenge required a complex solution, which is why the simple technique in the article caught my eye. I guess the article doesn't mention that solving the Crystal side of the problem is 10% of the work while solving the SP side of the problme is 90% of the work.

Thanks again

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/