SSRS 2008 存储过程未返回正确的记录集

Question

当我在 VS 2008 中创建报表并尝试使用此存储过程时，似乎我只得到一条类似于插入命令的记录。我想要取回的是 #WIP

BEGIN
SET NOCOUNT OFF


DECLARE @BeginDate datetime 
DECLARE @EndDate datetime --- Make sure month no 12 
Declare @NextMonth INT
set @NextMonth = @Month + 1  --- Make sure month no 12
set @BeginDate = convert(datetime, convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month),2) + '01' ) ;
set @EndDate = convert(datetime, convert(varchar(4),@Year) +  right('00' + convert(varchar(2),@NextMonth),2) + '01' ) ;

DECLARE @OwnerName nvarchar(50) 
DECLARE @Value numeric(18,2)
DECLARE @Hours numeric(18,2)
DECLARE @Expenses numeric(18,2)
DECLARE @Discount numeric(18,2)
DECLARE @InvoceTotal numeric(18,2)
DECLARE @Progress numeric(18,2)
DECLARE @ActualBilled numeric(18,2)
DECLARE @MyCursor CURSOR
Create Table #WIP (
    OwnerName varchar(50)
    ,BeginWIP numeric(18,2) 
    ,EndingWIP numeric(18,2)            
    ,PeriodAll numeric(18,2)            
    ,PeriodCurent numeric(18,2)             
    ,UnnatachedTime numeric(18,2) 
    ,Progress numeric(18,2) 
    ,Discount numeric(18,2)             
    ,NewHours numeric(18,2) 
    ,FeesStandard numeric(18,2) 
    ,ActualBill numeric(18,2) 
    ,Expenses numeric(18,2) 
    ,TotalInvoice numeric(18,2)             
    ,Real numeric(18,2) )   
SET @MyCursor = CURSOR 
    FOR
            select a.owneridname 
            ,Sum(ISNULL(t1.tcpm_hoursentered,0))  as hrs ,SUM(t1.tcpm_billingatstandardrate) as Standardbilled
            ,SUM(t1.tcpm_ActualBilledAmount) as ActualBilled
            ,SUM(t1.tcpm_actualbilledamount) as Invoicetotal
            from Filteredtcpm_timeItemValue t1
                    inner join Filteredtcpm_businessperiod b on t1.tcpm_businessperiodid = b.tcpm_businessperiodid
                    inner join FilteredSalesOrder s  on t1.tcpm_projectid = s.salesorderid
                    inner join FilteredAccount a on s.accountid = a.accountid and a.statecode=0
                    where b.tcpm_startdate >= @BeginDate 
                        and t1.tcpm_lastwipaction not in ('267120007','267120008','267120009') and  t1.tcpm_hoursentered IS not null
                group by a.owneridname      

    --OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor INTO @OwnerName,@Hours,@Value,@ActualBilled,@InvoceTotal

    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        FETCH NEXT FROM @MyCursor 
        IF EXISTS(select 1 from #WIP  where OwnerName = @OwnerName)
         BEGIN
            UPDATE #WIP 
            SET PeriodCurent=ISNULL(@Value,0)
            ,NewHours= @Hours
            ,ActualBill=@ActualBilled
            ,TotalInvoice=@InvoceTotal
             WHERE OwnerName = @OwnerName
         END
        IF NOT EXISTS(select 1 from #WIP  where OwnerName = @OwnerName)
         BEGIN
            INSERT INTO #WIP (OwnerName ,NewHours,ActualBill,TotalInvoice,PeriodCurent) 
                VALUES(@OwnerName,@Hours,@ActualBilled,@InvoceTotal,ISNULL(@Value,01)) 
         END         
        FETCH NEXT FROM @MyCursor INTO @OwnerName,@Hours,@Value,@ActualBilled,@InvoceTotal
    END     
select OwnerName ,BeginWIP,NewHours,ActualBill,TotalInvoice,PeriodCurent,UnnatachedTime  From #WIP

END的记录

Answer 1

你有一个单独的 FETCH NEXT ，在 while 循环的顶部没有 into 。

WHILE @@FETCH_STATUS = 0 
BEGIN 
    FETCH NEXT FROM @MyCursor

它正在为游标中的每隔一行创建一个结果集。评论出来，我想你会得到你想要的。

另外，由于额外的提取，您只处理游标的每隔一行。如果这是您的意图，那么您将需要在获取中添加一个并将这些值推入变量中。