从游标获取记录时出现问题 [英] problem fetching record from cursor

问题描述

大家好，我的sp在下面给出

hi every one, my sp is given below

ALTER PROCEDURE [dbo].[Imp_DSLNM] 
@id AS VARCHAR(Max),
@Sdate as Datetime
as
Declare @dflag int
Declare dst_cursor CURSOR FOR 
select dflag from RStatus where DLDate =@Sdate and RCode in (@id) and dflag=0
begin
create table #tempTable1 (AICode numeric(14,0),RCode numeric(14,0),[date] DateTime,LoadPN int,CS numeric(14,0)
,upcS bigint,SCode bigint,TransactionDate datetime,ER numeric(16,0),CRC int,SPrice money,RPrice money)
insert into #tempTable1 select IM.AICode,RM.RCode,W.[Date],1 as LPN,W.Qty as CS,0 as UN,W. Qty *IM.upcS as TupcS,
Im.upcS,W.SCode,getdate() as TransactionDate,W. ER,1 as CRC,IM.DSPrice,IM.DRPrice
from tblERP W inner join RMaster RM on W.SCode=RM.SCode
inner join IMaster IM on W.MCode=IM.ACode where RM.RCode in (20403,20107) and W.[Date]=@ddate
if(exists(select Scode from RMaster where RCode in (@id)))
begin
BEGIN TRY
  BEGIN TRANSACTION
 
    INSERT INTO SLDetail
    (
      icode,RCode,ddate,LPN,CS,Un,TUN,UPCs,SCode,SPrice,RPrice,transactionDate,ER,CCode
    )
    SELECT r.ACode ,r.RCode ,r.[date],R.LPN,r.[case],r.UN,r.TUn,r.upcs,r.SCode,r.SPrice ,r.RPrice,r.TransactionDate,r.ER,r.CRC 
    FROM #tempTable1 R
    LEFT JOIN SLDetail M ON R.[date] = M.dDate AND R.AICode = M.ICode AND R.SCode = M. Code 
    WHERE M.RCode is null 
    --Update the flag in source table after insert
    UPDATE tblERP SET [Status]= 1   WHERE [Status] = 0  and [Date]=@ddate

   COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
	  ROLLBACK TRANSACTION
    END CATCH
 END
  begin
	open dst_cursor
 	FETCH NEXT FROM dst_cursor INTO @dflag
 	WHILE @@FETCH_STATUS = 0 
	MERGE SLDetail AS M
	USING (SELECT * FROM #tempTable1 ) AS r
	ON M.Rcode=r.Rcode and  r.[date]=M.ddate and r.AICode=M.icode
	WHEN  MATCHED THEN UPDATE 
	set CS=r.CS,
	tun=r.TUns;
	--update statement for source table
	--WHEN NOT MATCHED THEN
	--insert 
	FETCH NEXT FROM dst_cursor INTO @dflag
end
end
drop table #tempTable1
CLOSE dst_cursor
DEALLOCATE dst_cursor

Go

查询-从RStatus中选择dflag，其中DLDate = @ Sdate和(@id)中的RCode和dflag = 0

1.上面返回两个记录，但是我的光标只能获取一个记录并进行更新.
2.当我运行此过程时，它只会执行而不会完成.

请给我上述问题的解决方案，如果您有其他替代cursur的方法，请说明如何使用其他方法代替上面的光标.

感谢

the Query --select dflag from RStatus where DLDate =@Sdate and RCode in (@id) and dflag=0

1. the above Return two record but my cursor only fetch one record and update.
2. when i run this procedure then its only executing and not finish.

please give me the solution of the above issue and if u have any other way instead of cursur please explain how can use another way in place of above cursor.

thanks

推荐答案

您的while循环丢失了-开始END

Your while loop is missing BEGIN - END

WHILE @@FETCH_STATUS = 0 

BEGIN -- Add this 

	MERGE SLDetail AS M
	USING (SELECT * FROM #tempTable1 ) AS r
	ON M.Rcode=r.Rcode and  r.[date]=M.ddate and r.AICode=M.icode
	WHEN  MATCHED THEN UPDATE 
	set CS=r.CS,
	tun=r.TUns;
	--update statement for source table
	--WHEN NOT MATCHED THEN
	--insert 
	FETCH NEXT FROM dst_cursor INTO @dflag

END -- Add This

这篇关于从游标获取记录时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！