How to pass multipe value to SP from Crystal

[vipinkrshamra]

Hi!!,

I have SP as follows

************SP****************
CREATE proc dbo.Test
(
@Emp as varchar(8000)
)
as
Select EmpId,Emp from tblEmp
Where
Emp in ( Select * from SplitString(@Emp,':::'))
************SP****************

I can execute above SP as follows from Query Analyzer

Test 'Jeny:::Lucy:::John'

But I am not able to pass parameter to SP from Crystal. If I pass value as

'Jeny:::Lucy:::John' or
Jeny:::Lucy:::John or
"Jeny:::Lucy:::John"

I am getting following error

***********Error********
'List of Values failure: fail to get values.[Cause of error: The length of the paramter field current values must be less than ot equal to the maximum values.]'

error sourcerompt.dll Error code: 0x8004380D
***********Error********

AM I missing something. Any idea is greatly apprecaited.

 

[Turkbear]

Hi,
Have you defined the parameter (in Crystal) to allow multiple values?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vidru]

Is the parameter set up as a dynamic parameter? If so, temporarily disable it, and try running the report again. We encountered numerous issues when reporting off of stored procedures and trying to use dynamic parameters. We eventually abandoned them altogether (at least within Crystal) and came up with our own solution.

-dave

 

[vipinkrshamra]

Hi Dave,

Thx for input. Yes I am usign dynamic parameters. If you dont mind could you share your workaround. I am using Crystal Enpterprise to publish the report for users and dont have any .Net/VB application at present to display report. I know how to pass parameters from application and also can use PUSH model to pass dataset to report but since I m using CE to distribute the report to users I can not use this workaround.

Once again thx a lot for helping

 

[vidru]

This won't work for CE, but here goes anyway...

For each parameter that we want to be dynamic, we create a formula field with a naming convention something like "DynParam|{?@ParamName}". The text of the formula is either a SELECT statement or a stored procedure. At runtime, we loop through the formula fields collection for any of the "DynParam|" formulas. When one is found, we get the formula's text, execute the query (or stored procedure) using ADO, then populate the corresponding parameter field's default values with the results.

Ugly, yet effective...

-dave