cancel
Showing results for 
Search instead for 
Did you mean: 

Pass date range parameter to SQL stored procedure.

Former Member
0 Kudos

Hi,

I'd like to pass a date range parameter from Crystal Reports to a sql stored procedure. Does anyone know if this is possible?

I've had no problem passing standard datetime (single value) paramaters to and from but am struggling with getting a range value parameter to work.

Environment: Crystal Reports 10/XI and SQL 2000 MSDE version or SQL 2005 Express Edition.

Any help would be appreciated.

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Write your SP to include the Start and End date. You can't pass an array of dates into the SP the SP won't know what to do with them.

Former Member
0 Kudos

Hi James

A parameter with a date range does not display anything if we normally place it in the report.

However, we can display the start range and end range values at the run time within the report.

This can be done with the help of two formulas and then these formulas can further be used for the desired manipulation.

formula # 1 :

Minimum(?MyParameter)

formula # 2 :

Maximum(?MyParameter)

Here '?MyParameter' being the parameter with the date range.

And then these 2 formulas 'Formula # 1' and 'Formula # 2' can be used to pass on to the stored procedure.

Former Member
0 Kudos

>

> And then these 2 formulas 'Formula # 1' and 'Formula # 2' can be used to pass on to the stored procedure.

Can someone please demonstrate exactly how to use formula results as date parameters to a SQL stored procedure? Keep in mind, there are two parameters to the stored procedure.

I have gleaned this much: Use Add Command and insert the procedure with

EXEC ServerName.dbo.usp_sprocName;1 '{?StringParameter}'

but if I try to do

{CALL ServerName.dbo.usp_SprocName({@Formula1},{@Formula2})}

then it gives the error "No value given for one or more required parameters".

Both of the parameters are VARCHAR(50).

I have finally found this link: [http://msdn.microsoft.com/en-us/library/ms710248(VS.85).aspx|http://msdn.microsoft.com/en-us/library/ms710248(VS.85).aspx]

This Microsoft site defines the format of the ODBC escape sequences, but I still do not know how to convince Crystal to insert it's parameter results or formula results.

Pulling what's left of my hair out . . .

~ Shaun