Click here to Skip to main content
15,072,348 members
Please Sign up or sign in to vote.
2.33/5 (3 votes)
See more:
hi every one, my sp is given below
SQL
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


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
Posted
Updated 16-Jul-12 3:05am
v3

1 solution

Your while loop is missing BEGIN - END

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900