Passing a table-field value in Crystal to a Store Procedure in SQL Server

[Former Member]

I have been checking all over the interenet via searches and although some seem to come close to this, its still not what I want.

Essentially I need to pass value from Table-Field record (for each record read/selected) via a paramete to a Stored Procedure(SP) in SQL Server 2205/2008. I do NOT want to be prompted for a value for this parameter each time the report is run, simple pass the value in which will be used along with other select criteria to bring back one value for the report to use in a calcuation per record.

The value of the parameter is a date, but I understand it would be better to pass it in as a varchar(8) - 'YYYYMMDD' - and then reconvert it inside the SP, as follows:

In Crystal Reports 2008 SP3, I have a formula defined as,

trans_date = ToText ({F1ARS_STMT_WS_TRAN.TRANS_DATEI}, 'YYYYMMDD')

and essential just want to pass this to the SP below ... i.e. trans_date ---> @strTransDate

I then link the key fields [EXCH_RATE_TABLE_NAME] and [TRANS_CCY_CODE] to other tables in the Database Expert, and put [EXCH_RATE_AMT] on the report and use it to calculate what I want.

This works fine when the prompt comes up and I put in a proper date, but I don't what it to prompt, but simple pass the F1ARS_STMT_WS_TRAN.TRANS_DATEI in via the fornula/parameter and let teh SQL do the rest for each record selected..

*****************************************************************************

CREATE PROCEDURE [dbo].sp_GET_EXCH_RATE_AMT (@strTransDate varchar(8)) --use format 'YYYYMMDD' to represent the date as a string.

-- Add the parameters for the stored procedure here

-- @TransDate datetime = now

AS

declare @TransDate datetime

set @TransDate = CONVERT(DATETIME, @strTransDate, 112)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT [EXCH_RATE_TABLE_NAME], [TRANS_CCY_CODE], [EXCH_RATE_AMT]

FROM [F1CCY_EXCH_RATE]

WHERE [MAJOR_CCY_CODE] = 'BBD'

AND [START_DATEI] =

(

SELECT MAX([START_DATEI])

FROM [F1CCY_EXCH_RATE]

WHERE [MAJOR_CCY_CODE] = 'BBD'

AND [START_DATEI] <= @TransDate

)

END

GO

GRANT EXECUTE ON sp_GET_EXCH_RATE_AMT TO PUBLIC

GO

*****************************************************************************

Thanks for any help. Can't tell the headache this has caused my both literally and figuratively.

[former_member11696 · Accepted solution]

Hello,

I moved your post to the Report Design forum. Lots of SQL help in here...

I believe the problem is due to you using a Parameterized Stored Procedure. The first thing CR has to do is connect to your DB source which requires the date parameter before it can run the query to add the date filter, it's the SP that is prompting for the parameter. Therefore the report has not run so it can't get the field value from the report until you fill in the info for the SP. Catch 22 problem.... Which came first, the Chicken or the Parameter....

The report will work as you have noted but I don't know of anyway to refresh unless parameter is filled in again....

Jason has a lot of great solutions when it comes to these dilemmas, Possibly using a Command Object may help but I believe you will still run into the same issue....

Only way I can think of is to not use a parameter in the SP and let CR do the filtering client side. Of course this means all data is coming back to the client PC as you are likely trying to find a work around for.

Thank you

Don

[Former Member · Accepted solution]

Thanks Don, you seemed to have confirmed what I suspected, but now I understand a bit better, whilst not being any further in solving my problem.

Whilst waiting on Jason, whom you mentioned to have dealings with these urksome issues, might I ask if it would be useful for my to try to CALL the SP from within CR, using a SQL Expression, after defining a variable and setting it to the value needed to be passed during Record Selection, or would I be wasting my time?

Thanks again,

David. (VisionDB)

[Former Member · Accepted solution]

Of course, the other question hich I would have come with before I decided to got the SQL Command & SPcourse, was to simply use the main SQL ...

SELECT EXCH_RATE_AMT

FROM F1CCY_EXCH_RATE

WHERE MAJOR_CCY_CODE = 'BBD'

AND EXCH_RATE_TABLE_NAME = @ExchRateTableName

AND TRANS_CCY_CODE = @AccountCCYCode

AND START_DATEI =

(

SELECT MAX(START_DATEI)

FROM F1CCY_EXCH_RATE

WHERE MAJOR_CCY_CODE = 'BBD'

AND EXCH_RATE_TABLE_NAME = @ExchRateTableName

AND TRANS_CCY_CODE = @AccountCCYCode

AND START_DATEI <= @TransDate

)

Where the @ fields are CR formulas and assigned values from database field names during record selection, but I get an error stateing that these are unknown fields. I've even trid this using Sub-Report but then the value returned was blnak (for some reason) and any case it cannot be passed BACK to the CR main report ... more frustrating when you know how to do something and the tool being used (CR in this instant) seems to have limitations - hopefully there are a few undocumented tricks that can still be tried/applied.

Cheers.

[former_member11696 · Accepted solution]

You can't use CR formula in a Command Object. It runs before CR does so it's not aware of nor can it access CR formula.

So it doesn't matter what you do you can not get the value before the query runs in CR. Possibly using the main report to get the value and then pass/link it to the subreport that uses your SP.

Don