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

问题描述：

当我在VS 2008中创建areport并尝试使用此存储过程时，它看起来似乎只有一条看起来像插入命令的记录。我想回去是记录从#WIPSSRS 2008存储过程不返回正确的记录集

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 
      ,[email protected] 
      ,[email protected] 
      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

在SQL Server 2008上，您应该使用** MERGE **命令而不是逐行拼凑行游标！ –

他不仅可以使用'MERGE'，但是如果我正在阅读这个权限，RBAR光标只是检查是否存在并插入或更新到最近的值...只是哇... – Wil

答

你有一个孤独的NEXT没有提取到你的while循环的顶部。

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

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

也因为额外的提取，你只处理光标的每隔一行。如果这是你的意图，那么你将需要添加一个到获取并推动thoes值到变量。

谢谢，我盯着那整天都没有见过。 – BillTetrault

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

相关推荐