Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Executing/Passing parameters to a Stored Procedures based on user input

I have been trying to find a way around this with no success, I have a SP that takes a start and end time as input parameters, what I want to do is to pass variables to this SP based on the ‘Relative Date’ selected by the user e.g Today, Yesterday, Last Week

Here is what I’m trying to do on Crystal with no success, I would appreciate help on what I might be doing wrong or if the is a better alternative method

DECLARE @YStartDate datetime

DECLARE @YEndDate datetime

  SET @YStartDate = dateadd(day, datediff(day, 1, getdate()), 0) + '00:00:00'

  SET @YEndDate = dateadd(day, datediff(day, 1, getdate()), 0) + '23:59:59'

DECLARE @TStartDate datetime

DECLARE @TEndDate datetime

  SET @TStartDate = dateadd(day, datediff(day, 0, getdate()), 0) + '00:00:00'

  SET @TEndDate = dateadd(day, datediff(day, 0, getdate()), 0) + '23:59:59'

Case '{?RelativeDate}'

When ‘Today’ then         EXEC PDV_UserWorkGroupInterval  @TStartDate, @TEndDate

  When 'Yesterday' Then EXEC PDV_UserWorkGroupInterval @YStartDate, @YEndDate

  When 'DateRange' Then CONVERT(datetime, {?StartDate})

END

Former Member
replied

The only way you'll be able to calculate this in the report in order to send it to the stored proc will be to put the stored proc into a subreport and pass the parameters in from the main report.  There is no way to calculated this in the main report prior to calling the stored proc.

If you don't need any other subreports in your report, this should not be a problem.  You'll use a table or command that returns a single record (I like to use something like "Select GetDate()" for SQL Server or "Select Sysdate from dual" for Oracle).  Put the parameters in the main report.  Then calculate the actual start and end date in separate formulas based on the parameter.  Use these formulas to link to the store proc parameters in the subreport.

-Dell

1 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question