Skip to Content
0
Former Member
May 27, 2011 at 09:41 PM

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

392 Views

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.