Pass Multiple Values to SQL Proc

[marydn]

Hello,

I am trying to pass a muliple value parameter to my SQL proc and I am not sure how to accomplish this.

What I am trying to do is create a do loop that pulls in every value selected by the user then pass that value with all the approriate quotes, etc...to the proc.

So If the user selects A, B, C Then the string will look like:

('A', 'B', 'C')

and in the proc the code is:

Select *
From x
where Value IN @Parameter

I am sure this is possible. I guess part of my confusion is on how to make the parameter accept the formula value I have to create through Crystal.

If someone can help me I would be so appreciative.....

M.

 

[vidru]

Do you plan on using the procedure as the data source for the report? If so, then you won't be able to utilize the 'Allow multiple values' feature for your parameter (Crystal disables it if the parameter is coming from a stored proc).

There are certainly ways to get this done. I am usually a strong proponent of using stored procs, but this may be an instance where you might want to use tables directly in order to take advantage of the multiple values option.

Post a little more info (Crystal version, database type, connectivity) and your procedure text (if you've written it yet, that is) and I'm sure we can find a solution.

-dave

 

[rhinok]

This isn't so much a Crystal Reports issue as it is a Stored Procedure issues. Stored Procedures generally won't accept multiple parameter values.

There are two work arounds, but they are kluges:[ol][li]Create enough parameters in both Crystal Reports and your stored procedure to handle the multiple values. Be careful of null parameter values.[/li]

[li]Allow for multiple selections within a discrete parameter value. Example: The user wants to select 'A', 'B' and 'C' for parameter values. Rather than select these values separately, the user must select them as part of a single string with some sort of standard delimiter (A+B+C, where '+' is the delimiter). This string is then passed to the stored procedure where a REPLACE (in SQL Server 2K) must be used to build the array that will be used by your IN statement. I've included some sample Proc code:

CREATE PROCEDURE p_sample
  @test_multivalue VARCHAR(254)

AS

DECLARE
  @v_base_query NVARCHAR(4000)
  @v_where NVARCHAR(4000)
  @v_multivalue_array VARCHAR(254)

SELECT
  @v_base_query = 'SELECT * FROM X'

SELECT
  @v_multivalue_array = REPLACE(RTRIM(@test_multivalue),'+',''',''')

SELECT
  @v_where = 'WHERE value IN (' + '''' + @v_multivalue_array + '''' + ') '

EXEC
  (@v_base_query + v_where)

In this scenario, the onus is on the user to enter the values--including the delimiters--correctly.[/li][/ol]~Kurt

 

[synapsevampire]

As you constructed the SP you probably noticed that it doesn't accept multiple values.

Add code to the SP to parse out values from what's passed, or use multiple parameters.

What you posted for the SP is NOT the SP.

Since all it is doing is selection *, it's not a particularly useful/efficient SP anyway, consider just using the table (or better yet a view, I never suggest using SP's) directly and using Crystal SP's, then you can leverage CR to pass multiple or range values using the record selection formula (report->edit selection formula->Record):

{table.field} = {?MyParm}

Crystal will pass all of the values and the performance will be similar to an SP.

You can test what's being passed using Database->Show SQL Query

-k

 

[rhinok]

I guess I was too literal. I addressed your exact question, whereas Vidru and synapsevampire both suggested what I was thinking. A View would probably work much better for you in this situation.

There are a lot of proponents for always using Stored Procedures as the basis for your reports. A well-written stored procedure will almost always be more efficient than a View or direct table joins, but there are some limitations, such as you one you've experienced:[ol][li]Stored Procedures can't accept multiple-value parameter input. I've had many clients that require the user to select either one, many or all values from a parameter prompt for their reports. This is not easily accomplished through the use of a stored procedure.[/li]

[li]Stored Procedures complicate linked sub reports. If you use the same parameterized Proc as the basis for the Main Report and a subreport, then you must enter the parameters for each report. If you used a View, the parameters would only have to be entered once.[/li][/ol]Views are generally more flexible, albeit less efficient than Procs. I typically write Views broadly enough to handle groups of reports that require similar data. Each report can pass in its own specific criteria, which enhances the effiency of the View by filtering more of the data. This can also be more efficient--from a maintenance standpoint--than writing one View/Report or for embedding the table joins directly within each report.

I generally reserve Stored Procs for extremely complex data sets that require heavy crunching. Stored Procs are generally written for very specific reports instead of suites of reports. As synapsevampire stated, if your proc only does 'Select * From Where In', then you're aren't gaining any benefits from using a proc. Instead, you're inheriting some of the negatives.

 

[synapsevampire]

I must be losing it, this:

"Since all it is doing is selection *, it's not a particularly useful/efficient SP anyway, consider just using the table (or better yet a view, I never suggest using SP's) directly and using Crystal SP's, then you can leverage CR to pass multiple or range values using the record selection formula (report->edit selection formula->Record):
"

should have been:

Since all it is doing is selectiNG *, it's not a particularly useful/efficient SP anyway, consider just using the table (or better yet a view, I never suggest using TABLES) directly and using Crystal PARMETERS, then you can leverage CR to pass multiple or range values using the record selection formula (report->edit selection formula->Record):

-k

 

[marydn]

Thanks for all the responses. I apologize for not being more clear. First, I am using version 9.0. Second, I used the Select * for simplicity. In reality I am taking a lot of data from several tables, putting it in temp tables and creating a summary to be viewed through the report. Using views, etc.....I do not feel would really be suitable. I just wanted to be able to pass on the multiple selection to the where clause of each temp table that is created in the proc. I think I will just request the users to develop the different scenarios they would want to see and incorporate that in my proc.

Thanks again!


M.

 

[synapsevampire]

Why "I do not feel would really be suitable"?

I chastise my SQL developers for using the lazy select * (and for using tables) because if the underlying structure of the table changes, the report may fail.

This being another advantage to using Views.

If you base 20 reports and an application on a table which has a change to it's structure, all reports and the app will fail and need to be reworked.

If you base 20 reports on a View which points to a table, all 20 reports and the application will fail, but with a tweak to the View, all reports and the application now work.

So I write a script which generates Views for all tables, and ALL SQL developers only have access to the Views. That means that SP's are based on Views as well.

Again, your SP is buying you virtually nothing in performance increase, but it definitely limits your reusability and increases maintenance.

-k