Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal Reports not accepting parameter value when using a stored procedure

Status
Not open for further replies.

shannonlp

Technical User
Feb 9, 2006
163
US
I’m attempting to use a Sybase stored procedure in my Crystal Report. In my stored procedure I have parameter that I’ve defined as a date. However, Crystal Reports is recognizing it as a string.

When I refresh the report and uncheck ‘Set to Null’ and enter a date I get the following error:

“Failed to retrieve data from the database. Database Connector Error: ‘Procedure date_store expects parameter @dateparam, which was not supplied.”

I believe the syntax in my stored procedure is correct. Is there something on the Crystal Reports side I need to configure so that recognized that the @dateparam is a date, not a string?

I’m pasted my stored procedure below. I'm using Crystal Reports 12. Thanks in advance for any help.

create PROC dbo.date_store (@dateparam date)
AS
DECLARE @eff_date date

if @dateparam is null
select @eff_date = prev_bus_date from dbo.metric_sys_date
else
select @eff_date = @dateparam


select metric_collct.collct_desc, metric_collct.notes_desc, metric_collct_catgry.catgry_name, metric_hist.eff_date, metric_hist.numeric_qty, metric_hist.datetime_dttm, metric_hist.notes_text, metric_hist.hist_dttm
FROM
prod01.dbo.metric_collct metric_collct,
prod01.dbo.metric_collct_catgry metric_collct_catgry,
prod01.dbo.metric_hist metric_hist
WHERE
metric_collct.catgry_id = metric_collct_catgry.catgry_id AND
metric_collct.collct_id = metric_hist.collct_id AND
metric_collct_catgry.catgry_name = 'DCS' AND
metric_hist.eff_date = @eff_date
go
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top