使用日期参数在Crystal Reports 11中的命令对象中调用存储过程

I have a stored procedure that is in a command object inside a Crystal Report. I need to pass it a date. I defined startdate and enddate in the command parameter window.

我有一个存储过程在Crystal Report中的命令对象中。我需要通过它约会。我在命令参数窗口中定义了startdate和enddate。

This is how I defined the object parameters in Crystal:

这就是我在Crystal中定义对象参数的方法：

Startdate – string . default value = ‘t-1’
Enddate - string. default value = ‘t-1’

It does not error out but runs forever and I have to terminate it. What am I doing wrong?

它没有错误但是永远运行，我必须终止它。我究竟做错了什么？

This is the code inside the command object in Crystal:

这是Crystal中命令对象内的代码：

DECLARE @StatisticStartDate as datetime = EPIC_UTIL.EFN_DIN({?startdate});
DECLARE @StatisticEndDate as datetime = dateadd(s, 86399, EPIC_UTIL.EFN_DIN({?enddate})); 
DECLARE 
        --@StatisticStartDate nvarchar(20), -- Change all of these to the data types of the specific columns...
        --@StatisticEndDate nvarchar(20), 
        @CostCenterList nvarchar(200), 
        @ProcedureCodeList nvarchar(100), 
        @Statistic nvarchar(100),
        @StatisticDescription VARCHAR(255),
        @RVValue numeric(18,0),
        @Fiscalyear int,
        @PatientTypeIndicator nvarchar(100),
        @PatientServiceList nvarchar(100),
        @PatientClassList nvarchar(100),
        @PatientStatusList nvarchar(100);

DECLARE @Output TABLE (
                           [Facility ID] INT,
                           [Stat Date] DATETIME,
                           [DEPARTMENT] VARCHAR(200),
                           [Statistic Name] VARCHAR (200),
                           [Statistic Description] VARCHAR (200) ,
                           [Patient Type Indicator] VARCHAR (2),
                           [Daily Quantity] DECIMAL(18,2) ,
                           [Daily Amount] DECIMAL(18,2),
                           COST_CENTER_NAME VARCHAR(200) ,
                           [Fiscal Year] INT
                           )

DECLARE Setting_cur CURSOR FOR 
    SELECT 
       StatisticStartDate,
       StatisticEndDate, 
       CostCenterList, 
       ProcedureCodeList, 
       Statistic,
       StatisticDescription,
       PatientTypeIndicator,
       RVThreshold,
       PatientServiceList,
       PatientClassList,
       PatientStatusList,
       FiscalYear
   FROM 
       ssisconfiguration.dbo.BalladHealthDayOneStats
   --WHERE 
   --FiscalYear = 2017 
;

OPEN Setting_cur

FETCH NEXT FROM Setting_cur INTO @StatisticStartDate, @StatisticEndDate, 
                                 @CostCenterList, @ProcedureCodeList, 
                                 @Statistic, @StatisticDescription,
                                 @PatientTypeIndicator, @RVValue,
                                 @PatientServiceList, @PatientClassList,
                                 @PatientStatusList, @FiscalYear

WHILE @@FETCH_STATUS = 0 
BEGIN
    INSERT @Output
        EXEC [SSIS].[sp_BalladDayOneMaster] --@RCout = Clarity.ssis.sp_GetFinancialStatsByFinParams 
                           @SD = @StatisticStartDate,
                           @ED = @StatisticEndDate,
                           @CostCenterList = @CostCenterList,
                           @ProcedureCodeList = @ProcedureCodeList,
                           @Statname = @Statistic,
                           @StatDescription = @StatisticDescription,
                           @PatientTypeIndicator = @PatientTypeIndicator,
                           @RVvalue = @RVValue,
                           @FiscalYear = @FiscalYear, 
                           @PatientClassList = @PatientClassList , 
                           @PatientStatusList = @PatientStatusList, 
                           @PatientServiceList = @PatientServiceList  

    FETCH NEXT FROM Setting_cur INTO @StatisticStartDate, @StatisticEndDate, 
                                     @CostCenterList, @ProcedureCodeList, 
                                     @Statistic, @StatisticDescription,
                                     @PatientTypeIndicator, @RVValue,
                                     @PatientServiceList, @PatientClassList,
                                     @PatientStatusList, @FiscalYear
END

CLOSE Setting_cur
DEALLOCATE Setting_cur

SELECT * FROM @Output

1 个解决方案

#1

Instead of insert all the code inside the command object I would suggest you to create the stored procedure and select that one when you create the new report, so you can debug the stored procedure from sql server.

我建议你创建存储过程并在创建新报表时选择那个，而不是在命令对象中插入所有代码，这样就可以从sql server调试存储过程。