himel habibullah himel habibullah - 2 months ago 4
SQL Question

while @@FETCH_STATUS=0 is not working

See below my trigger that is not working. I have tested this many times and the trigger is working, but not the cursor. I do get a value from

inserted
but it does not execute
WHILE (@@FETCH_STATUS = 0)


ALTER TRIGGER [dbo].[TRG_InsertStockMasterDetail]
ON [dbo].[PrdBallMRRConsumption]
AFTER INSERT AS

Begin


Declare @IssueTypeID Int, @IssueID bigint, @IssueDate dateTime, @SupplierID int, @DepartmentID int,
@CompanyID int, @CreateBy int, @CreateDate Datetime, @CreatePC nvarchar(50), @IsDeleted bit,
---------------------------------------------------------------------------------------------------------------------------------------
@ItemID int , @UOMID int, @LotID int, @Qty Decimal(18,6),
@UnitPrice Decimal(18,6), @ItemTypeID int, --@BatchID int, @GradeID int,
---------------------------------------------------------------------------------------------------------------------------------------
@ReceiveValue Decimal(18,6), @ReceiveQty Decimal(18, 6), @IssueQty Decimal(18,6), @IssueValue Decimal(18,6), @StockID bigint,
@CurrentStock numeric(18, 6), @CurrentRate numeric(18, 10), @CurrentStockValue numeric(18, 6),
@IsStockUpdate bit, @IsCostingUpdate bit

----/// Declare Cursour ///----
--select BallConsumptionID, YarnCountID, SupplierID, DepartmentID, LotID, LengthM, LengthYds, UnitPrice, Amount, Qty, Unit, Remarks, CompanyID, CreateBy, CreateOn, CreatePc, UpdateBy, UpdateOn, UpdatePc, IsDeleted, DeleteBy, DeleteOn, DeletePc from PrdBallMRRConsumption
DECLARE Issue_Insert CURSOR FOR
Select M.TransactionTypeID,D.BallConsumptionID,M.BalMRRDate,D.SupplierID,D.DepartmentID,D.CompanyID,D.CreateBy,D.CreateOn,D.CreatePc,D.IsDeleted,
D.YarnCountID ItemID, D.Unit UnitID,D.LotID,D.Qty,D.UnitPrice,(ISNULL(D.Qty,0) * ISNULL(D.UnitPrice,0)) AS ReceiveValue

from Inserted D --PrdBallMRRConsumption
INNER JOIN PrdBallMRRDetail S ON D.BallConsumptionID=S.BallConsumptionID
INNER JOIN PrdBallMRRMaster M ON S.BalMRRID=M.BalMRRID

OPEN Issue_Insert
FETCH NEXT FROM Issue_Insert
INTO @IssueTypeID ,@IssueID ,@IssueDate,@SupplierID ,@DepartmentID , @CompanyID,@CreateBy, @CreateDate,@CreatePC,@IsDeleted,
-------
@ItemID , @UOMID, @LotID, @Qty, @UnitPrice, @ReceiveValue
------



----/// Loop Cursour ///----
WHILE (@@FETCH_STATUS = 0)

BEGIN

Select @ItemTypeID = ItemTypeID from CmnItemMaster where itemid=@ItemID
select @IsStockUpdate=IsStockUpdate,@IsCostingUpdate=IsCostingUpdate
from CmnTransactionTypeERPIntigration
Where TransactionTypeID=@IssueTypeID AND CompanyID=@CompanyID


IF(@IsStockUpdate=1)
BEGIN
Select @ReceiveQty =0, @CurrentStock =0, @CurrentRate =0, @CurrentStockValue=0



IF(ISNULL(@ItemID,'') <> '')
Begin
--1 For Random , 2 For FIFO, 3 For LIFO
IF((SELECT TOP 1 IntegrationType FROM InvIntegrationType
Where (ISNULL(DepartmentID,0)=0 OR DepartmentID=@DepartmentID)
and CompanyID=@CompanyID)='AVG')


BEGIN

Update InvStockMaster Set ReceiveQty = Isnull(ReceiveQty,0) - @Qty, ReceiveValue = Isnull(ReceiveValue,0) - @ReceiveValue,
LastReceiveDate= Case When Isnull(LastReceiveDate,@IssueDate)<=@IssueDate Then @IssueDate Else LastReceiveDate End
Where Isnull(CompanyID,0)=Isnull(@CompanyID,0)
And Isnull(DepartmentID,0) = Isnull(@DepartmentID,0)
And Isnull(ItemID,0) = Isnull(@ItemID,0)
And Isnull(UOMID,0) = Isnull(@UOMID,0)
And Isnull(SupplierID,0) = Isnull(@SupplierID,0)
--AND ISNULL(BatchID,0)=ISNULL(@BatchID,0)
AND ISNULL(LotID,0)=ISNULL(@LotID,0)
--AND ISNULL(GradeID,0)=ISNULL(@GradeID,0)

Select @StockID = StockID from InvStockMaster
Where Isnull(CompanyID,0)=Isnull(@CompanyID,0)
And Isnull(DepartmentID,0) = Isnull(@DepartmentID,0)
And Isnull(ItemID,0) = Isnull(@ItemID,0)
And Isnull(UOMID,0) = Isnull(@UOMID,0)
And Isnull(SupplierID,0) = Isnull(@SupplierID,0)
--AND ISNULL(BatchID,0)=ISNULL(@BatchID,0)
AND ISNULL(LotID,0)=ISNULL(@LotID,0)
--AND ISNULL(GradeID,0)=ISNULL(@GradeID,0)

INSERT INTO InvStockDetail
(StockID, TransactionID, TransactionTypeID, StockDate, ItemID, ItemTypeID, LotID, SupplierID, DepartmentID, ReceiveQty, ReceiveRate, ReceiveValue, UOMID, CompanyID, CreateBy, CreateOn, CreatePc, IsDeleted)
Values (@StockID, @IssueID, @IssueTypeID, @IssueDate, @ItemID, @ItemTypeID, @LotID, @SupplierID, @DepartmentID, @Qty, @UnitPrice, @ReceiveValue, @UOMID, @CompanyID, @Createby, @CreateDate, @CreatePC, @IsDeleted)



----// For Average Cost //----
--Begin
IF(@IsCostingUpdate=1)
BEGIN
Update InvStockMaster Set CurrentRate = CurrentValue/CurrentStock
Where Isnull(CurrentValue,0) <> 0 And Isnull(CurrentStock,0)<>0
AND Isnull(CompanyID,0)=Isnull(@CompanyID,0)
And Isnull(DepartmentID,0) = Isnull(@DepartmentID,0)
And Isnull(ItemID,0) = Isnull(@ItemID,0)
And Isnull(UOMID,0) = Isnull(@UOMID,0)
And Isnull(SupplierID,0) = Isnull(@SupplierID,0)
--AND ISNULL(BatchID,0)=ISNULL(@BatchID,0)
AND ISNULL(LotID,0)=ISNULL(@LotID,0)
--AND ISNULL(GradeID,0)=ISNULL(@GradeID,0)


END
END
--Comments
END


END

FETCH NEXT FROM Issue_Insert
INTO @IssueTypeID , @IssueID , @IssueDate , @SupplierID , @DepartmentID ,
@CompanyID , @CreateBy , @CreateDate , @CreatePC, @IsDeleted ,
-------------------------------------------------------------------------------------------------------
@ItemID , @UOMID , @LotID , @Qty , @UnitPrice , @ReceiveValue
--@BatchID, @GradeID ,
End
CLOSE Issue_Insert
DEALLOCATE Issue_Insert

End

Answer

Seems like the structure is correct, only thing I can think of is data related, there is no records to fetch next from, make sure the query below returns more than one row

 Select M.TransactionTypeID,D.BallConsumptionID,M.BalMRRDate,D.SupplierID,D.DepartmentID,D.CompanyID,D.CreateBy,D.CreateOn,D.CreatePc,D.IsDeleted,
D.YarnCountID ItemID, D.Unit UnitID,D.LotID,D.Qty,D.UnitPrice,(ISNULL(D.Qty,0) * ISNULL(D.UnitPrice,0)) AS ReceiveValue 

from  Inserted D --PrdBallMRRConsumption
INNER JOIN PrdBallMRRDetail S ON D.BallConsumptionID=S.BallConsumptionID
INNER JOIN PrdBallMRRMaster M ON S.BalMRRID=M.BalMRRID

I also looked at my code now and I don't put brackets on my while statement

------------------------------  
--  CURSOR FOR MULTISITES   --
------------------------------
-- create the dynamic sql to exec data from the appropriate sites   
declare @sql_table table (sql_cmd nvarchar(max), site_id int)  
declare @sql nvarchar(max), @sql_siteid int  

insert @sql_table (sql_cmd, site_id)  
select   
    'exec '+s.name+'.dbo.[proc_To_run] @MarketingCampaignID,@siteid,@start_date,@end_date'
    ,a.site_id  
from aux_site a  
inner join cnt_this_company_sites cs on cs.siteid = a.site_id
inner join sys.databases s on dbo.sitename(a.site_name) = s.name  
where is_active = 1  


DECLARE __curs_MultiSite CURSOR FAST_FORWARD FOR   
SELECT sql_cmd, site_id  from @sql_table  

open __curs_MultiSite  

fetch next from __curs_MultiSite into @sql, @sql_siteid  

while @@fetch_status = 0  
begin       
    insert into #ME_Data       
    EXECUTE SP_EXECUTESQL            --  This method of exec-ing is supposedly SQL Injection safe  
    @sql              --  The sql to exec  
    ,N'@MarketingCampaignID INT, @SiteId INT, @start_date DATETIME, @end_date DATETIME'  --  Define the params that occur within @sql_current   
    ,@MarketingCampaignID
    ,@sql_siteid
    ,@start_date
    ,@end_date                  

    --reseed
    fetch next from __curs_MultiSite into @sql, @sql_siteid   
end    
close __curs_MultiSite   
deallocate __curs_MultiSite     
--END CURSOR __curs_MultiSite