Hi... I'm having problems passing a 'dynamic' parameter to a stored procedure: the "Create" parameters seems to allow only simple data strings, and not variables or other values entered/created at run time.

The report uses a database command which simply executes a procedure:
   EXECUTE MyStoredProcedure;
The procedure has two parameters: startDate and endDate, both of which are defaulted in the stored proc.

The report prompts the user for a start date and an end date (?StartDate & ?EndDate).

Right now, using the default values in the stored proc, everything is running just fine.

I want to pass these two values to the stored procedure so that when the user selects the desired date range, it passes on thru to the stored procedure.

I don't see how to pass in the values that are already entered (I don't want the user to have to enter the same dates twice). It looks like the prompt has to happen and that they will have to enter the dates twice.

Any suggestions would be most appreciated, including a definitive statement that "you can't do that" so I don't waste more time looking for an impossible solution.

In fact, I can't even get a simple test run of the parameters to work: CR complains with "Error converting data type nvarchar to datetime" (my parameters are smalldatetime types, which means only date, no time).

Cheers...jon
      

?StartDate & ?EndDate are the standard CR variables for parameters that are prompted for by CR at the beginning of the run.

The report uses the proc and works just fine using the procs default start/end dates.

I want to pass along CR's start/end date parameter values to the proc, using:
EXECUTE MyStoredProcedure sDate, eDate;
where sDate/eDate contain the values of ?StartDate, ?EndDate.

That's what I can't see how to do...

"just choose your start/end date from the CR run window"
>> That's exactly what I did. In case you're not familiar with this feature, those values are referenced as ?StartDate & ?EndDate...

"it should accept the dates provided from the CR"
>> It should/ Tell me how that happens and you've solved my problem.

Why don't you try creating a stored proc that requires a single parameter, and then use the Command option in the database expert to execute the stored proc. Then run the report, get the value of that single parameter, and then pass that parameter from the CR run window into the stored proc. Maybe that will help clarify the issue for you.



 

Yes, I've also used the stored proc: two issues there...

1) user gets prompted twice: once for the CR parameter and once for the SQL parameters

2) If there are defaults in the stored proc, then CR doesn't prompt for them.

1) You only use the params that were created via the SP. Delete the ones you manually created in crystal.